Trigger
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Trigger as PDF for free.
More details
- Words: 1,321
- Pages: 31
MODULE VI VIEWS, PROCEDURES AND TRIGGERS
Views • Views – – – – –
Definition of View Creating Views Selecting a contents of View Modify data using Views Aggregate Functions
Definition of View • Can be defined as virtual tables • Contains Subset of data from a table or two or more than two • Does not store any data • Picks up the information from the table and displays it
Creation of View • Syntax CREATE VIEW AS SELECT FROM table_name>[, table_name]
• Example CREATE VIEW emp_view AS SELECT ename,empno FROM emp where deptno=30 View must not have same name as base table or any other view
Creating View From Multiple Tables • Example CREATE VIEW emp_dept_view AS SELECT empno, ename, deptno, dname FROM emp e, dept d WHERE e.deptno=d.deptno
• When View is created from multiple tables a intermediary table is created to with all the combinations • In the above example intermediary table contains empno, ename, deptno, dname
Selecting View Contents • Syntax SELECT FROM view_name WHERE condition
• Example SELECT ename FROM emp_view where empno=10
•
Modification Through Views • • • •
Data can be modified through views Modifications are done at table level Update can be done for only one table at a time Update cannot be done on view that has a computed Column • Example UPDATE emp_view SET SAL=20 WHERE ename=‘SMITH’
Aggregation Functions • Aggregation functions such as MIN, MAX, AVG etc., can be used views • If the first select statement has distinct key word or a column function such as MAX then view is read only • View with sub- queries is also ready only • Example – CREATE VIEW emp_view1 AS – SELECT AVG(sal) FROM emp – GROUP BY deptno
Control Flow Statements • DECLARE - is used to declare a variable – Syntax DECLARE – Example DECLARE @name
• BEGIN … END - are used to enclose the program statements. Usually called block • Every Block should have BEGIN and END • Syntax BEGIN <statements> <statements> END
If else statement • Usually called conditional statement • Takes Boolean Expression as as argument • Based on the condition if block is executed otherwise else block is executed – Example DECLARE @age int SET @age=20 IF @age < 10 BEGIN Print cast(@age as varchar(20) ) + ‘less than 10’ ELSE Print cast(@age as varchar(20) ) + ‘greater than 10’
While Loop • Used to execute a set of statement repeatedly • Takes Boolean expression as argument • Remains in the loop as long as expression is True Syntax WHILE boolean_expr BEGIN <Statements> <Statements> END
Break and Continue • Break statement can be used to exit the loop unconditionally • Continue Statement is used to re-evaluate Boolean Expression and start loop from the beginning discarding the statements after continue WHILE Boolean_expression BEGIN Statements CONTINUE Statements BREAK END
DECLARE @P INT Declare @ s int set @s =20 while(@s <30) begin set @p=@s +1 continue
Goto, Return and WaitFor • Goto – is used to branch to a defined label – Used for error handling
• Return – – – – –
used to stop execution of batch, trigger, procedure takes an integer as an argument 0 - zero indicates successful execution -1 to -99 are reserved numbers user should specify out of this range
• WAITFOR – used to halt the execution for a specified delay (WAITFOR DELAY) or specified time (WAITFOR
Procedures • • • • • •
Creating Procedures Modification of Procedures Procedures with parameters Execution of Store Procedures System Stored Procedures Auto Execution of Store Procedures
Creating A Procedure • Procedures are created using CREATE • syntax CREATE procedure <procedure_name> AS select statements • Always created in the current database • It is executed using EXECUTE statement • It is like any other high level language procedure
• CREATE PROCEDURE empproc AS select * from emp WHERE sal >2000 EXECUTE empproc
Modification Of Procedures • Procedures can be modified using ALTER • Syntax of ALTER is same as CREATE but CREATE is replaced by ALTER • Example • ALTER PROCEDURE empproc • as select * from emp where sal>3000 and deptno=20
Deletion Of Procedure • Procedure can be deleted using DROP • Once if deleted, it no more exists in memory • Syntax: – DROP PROCEDURE <procedure name>
• Example – DROP PROCEDURE empproc
Procedures With Parameters • • • • •
Parameters can be passed to a procedure Parameters name must begin with @ symbol Parameter must be declared with a data type All parameters are treated as input parameters Output parameters must be specified with OUTPUT keyword • When procedure returns the output it will have last assigned value • OUTPUT keyword must also be specified with the argument in execute statement
Example Create procedure getdata @name varchar(20), @amount int output as set @amount=(select sal from emp where empname=@name) execute getdata ‘smith’ , @amount output
SYSTEM STORED PROCEDURES The stored procedures that are preceded with sp_ are said to be systemstored procedures. Some of the available system stored procedures are •Sp_help procedure name •Sp_helptext procedure name •Sp_depends procedure name •Sp_rename old procedure name New procedure name, object type
Displays the procedure’s owner and when it was created. Displays source code for the procedure Displays a list of objects that the procedure references Renames the existing procedure.
Triggers • Create and use Triggers – INSERT Trigger – UPDATE Trigger – DELETE Trigger
• Enforce data integrity through triggers – Data Integrity – Referential Integrity
• • • •
Business Rules Nested Triggers Altering Triggers Dropping Triggers
Creating Triggers • Is a special Stored Procedure executes when an event occurs such as INSERT, UPDATE, DELETE. • Stored as an object in database • Always attached to single table • Trigger cannot create on View • Syntax: CREATE TRIGGER [owner] trigger_name ON [owner] table_name [with Encryption ] {for {INSERT | UPDATE | DELETE } AS [IF UPDATE (column_name)…] [{AND | OR} UPDATE (column_name)…] SQL statement
Insert Trigger • • • •
Executed when a new row is inserted in a table New rows are stored in the inserted Table Inserted Table is a logical table One can compare data with the help of inserted Table Example CREATE TRIGGER data_ent ON sales FOR INSERT AS IF(SELECT COUNT(*) FROM item, inserted WHERE item.item_name=inserted.item_name)=0 rollback transaction
Delete Trigger • • • •
Executed when a row is deleted in a table New rows are stored in the deleted Table deleted Table is a logical table A row appended to deleted table does not exist in database table • Example CREATE TRIGGER del_trig ON item FOR DELETE AS DELETE purchase FROM purchase, deleted WHERE purchase.item_no=deleted.item_no
Update Trigger • • • •
Executed when a row is updated in a table New rows are stored in the inserted Table Original rows goes to the deleted table Update trigger can be written on column also Example CREATE TRIGGER upd_trig ON item FOR UPDATE AS IF UPDATE(item_no) rollback transaction
Enforcing Data Integrity •
Nested Triggers • A trigger can contain update, insert or delete statements that effect another table • Can be used to perform functions such as storage of back-up copies of rows effected by previous trigger • Configuration option is set to on by default. • Will not fire twice in the same transaction • If failed at any level all modifications are rolled back
Altering Triggers • Triggers that are created can be altered • To alter a trigger ALTER TRIGGER key word must be used • Used to change the existing definition of the trigger and replaced by a new definition • Example
Dropping Trigger • A trigger can be removed by dropping it • Triggers are dropped automatically when associated tables are dropped • Permission to drop trigger is available to owner, but system administrator and database owner can drop any object. • DROP TRIGGER
Views • Views – – – – –
Definition of View Creating Views Selecting a contents of View Modify data using Views Aggregate Functions
Definition of View • Can be defined as virtual tables • Contains Subset of data from a table or two or more than two • Does not store any data • Picks up the information from the table and displays it
Creation of View • Syntax CREATE VIEW
• Example CREATE VIEW emp_view AS SELECT ename,empno FROM emp where deptno=30 View must not have same name as base table or any other view
Creating View From Multiple Tables • Example CREATE VIEW emp_dept_view AS SELECT empno, ename, deptno, dname FROM emp e, dept d WHERE e.deptno=d.deptno
• When View is created from multiple tables a intermediary table is created to with all the combinations • In the above example intermediary table contains empno, ename, deptno, dname
Selecting View Contents • Syntax SELECT
• Example SELECT ename FROM emp_view where empno=10
•
Modification Through Views • • • •
Data can be modified through views Modifications are done at table level Update can be done for only one table at a time Update cannot be done on view that has a computed Column • Example UPDATE emp_view SET SAL=20 WHERE ename=‘SMITH’
Aggregation Functions • Aggregation functions such as MIN, MAX, AVG etc., can be used views • If the first select statement has distinct key word or a column function such as MAX then view is read only • View with sub- queries is also ready only • Example – CREATE VIEW emp_view1 AS – SELECT AVG(sal) FROM emp – GROUP BY deptno
Control Flow Statements • DECLARE - is used to declare a variable – Syntax DECLARE
• BEGIN … END - are used to enclose the program statements. Usually called block • Every Block should have BEGIN and END • Syntax BEGIN <statements> <statements> END
If else statement • Usually called conditional statement • Takes Boolean Expression as as argument • Based on the condition if block is executed otherwise else block is executed – Example DECLARE @age int SET @age=20 IF @age < 10 BEGIN Print cast(@age as varchar(20) ) + ‘less than 10’ ELSE Print cast(@age as varchar(20) ) + ‘greater than 10’
While Loop • Used to execute a set of statement repeatedly • Takes Boolean expression as argument • Remains in the loop as long as expression is True Syntax WHILE boolean_expr BEGIN <Statements> <Statements> END
Break and Continue • Break statement can be used to exit the loop unconditionally • Continue Statement is used to re-evaluate Boolean Expression and start loop from the beginning discarding the statements after continue WHILE Boolean_expression BEGIN Statements CONTINUE Statements BREAK END
DECLARE @P INT Declare @ s int set @s =20 while(@s <30) begin set @p=@s +1 continue
Goto, Return and WaitFor • Goto – is used to branch to a defined label – Used for error handling
• Return – – – – –
used to stop execution of batch, trigger, procedure takes an integer as an argument 0 - zero indicates successful execution -1 to -99 are reserved numbers user should specify out of this range
• WAITFOR – used to halt the execution for a specified delay (WAITFOR DELAY) or specified time (WAITFOR
Procedures • • • • • •
Creating Procedures Modification of Procedures Procedures with parameters Execution of Store Procedures System Stored Procedures Auto Execution of Store Procedures
Creating A Procedure • Procedures are created using CREATE • syntax CREATE procedure <procedure_name> AS select statements • Always created in the current database • It is executed using EXECUTE statement • It is like any other high level language procedure
• CREATE PROCEDURE empproc AS select * from emp WHERE sal >2000 EXECUTE empproc
Modification Of Procedures • Procedures can be modified using ALTER • Syntax of ALTER is same as CREATE but CREATE is replaced by ALTER • Example • ALTER PROCEDURE empproc • as select * from emp where sal>3000 and deptno=20
Deletion Of Procedure • Procedure can be deleted using DROP • Once if deleted, it no more exists in memory • Syntax: – DROP PROCEDURE <procedure name>
• Example – DROP PROCEDURE empproc
Procedures With Parameters • • • • •
Parameters can be passed to a procedure Parameters name must begin with @ symbol Parameter must be declared with a data type All parameters are treated as input parameters Output parameters must be specified with OUTPUT keyword • When procedure returns the output it will have last assigned value • OUTPUT keyword must also be specified with the argument in execute statement
Example Create procedure getdata @name varchar(20), @amount int output as set @amount=(select sal from emp where empname=@name) execute getdata ‘smith’ , @amount output
SYSTEM STORED PROCEDURES The stored procedures that are preceded with sp_ are said to be systemstored procedures. Some of the available system stored procedures are •Sp_help procedure name •Sp_helptext procedure name •Sp_depends procedure name •Sp_rename old procedure name New procedure name, object type
Displays the procedure’s owner and when it was created. Displays source code for the procedure Displays a list of objects that the procedure references Renames the existing procedure.
Triggers • Create and use Triggers – INSERT Trigger – UPDATE Trigger – DELETE Trigger
• Enforce data integrity through triggers – Data Integrity – Referential Integrity
• • • •
Business Rules Nested Triggers Altering Triggers Dropping Triggers
Creating Triggers • Is a special Stored Procedure executes when an event occurs such as INSERT, UPDATE, DELETE. • Stored as an object in database • Always attached to single table • Trigger cannot create on View • Syntax: CREATE TRIGGER [owner] trigger_name ON [owner] table_name [with Encryption ] {for {INSERT | UPDATE | DELETE } AS [IF UPDATE (column_name)…] [{AND | OR} UPDATE (column_name)…] SQL statement
Insert Trigger • • • •
Executed when a new row is inserted in a table New rows are stored in the inserted Table Inserted Table is a logical table One can compare data with the help of inserted Table Example CREATE TRIGGER data_ent ON sales FOR INSERT AS IF(SELECT COUNT(*) FROM item, inserted WHERE item.item_name=inserted.item_name)=0 rollback transaction
Delete Trigger • • • •
Executed when a row is deleted in a table New rows are stored in the deleted Table deleted Table is a logical table A row appended to deleted table does not exist in database table • Example CREATE TRIGGER del_trig ON item FOR DELETE AS DELETE purchase FROM purchase, deleted WHERE purchase.item_no=deleted.item_no
Update Trigger • • • •
Executed when a row is updated in a table New rows are stored in the inserted Table Original rows goes to the deleted table Update trigger can be written on column also Example CREATE TRIGGER upd_trig ON item FOR UPDATE AS IF UPDATE(item_no) rollback transaction
Enforcing Data Integrity •
Nested Triggers • A trigger can contain update, insert or delete statements that effect another table • Can be used to perform functions such as storage of back-up copies of rows effected by previous trigger • Configuration option is set to on by default. • Will not fire twice in the same transaction • If failed at any level all modifications are rolled back
Altering Triggers • Triggers that are created can be altered • To alter a trigger ALTER TRIGGER key word must be used • Used to change the existing definition of the trigger and replaced by a new definition • Example
Dropping Trigger • A trigger can be removed by dropping it • Triggers are dropped automatically when associated tables are dropped • Permission to drop trigger is available to owner, but system administrator and database owner can drop any object. • DROP TRIGGER
Related Documents
Trigger
November 2019 31
Trigger
November 2019 30
Trigger
June 2020 22
Trigger
November 2019 31
Trigger
November 2019 39
Trigger Finger.docx
October 2019 19More Documents from "Muhammad Fadhil Amrullah"
Basic Shortcut Keys (microsoft Products)
November 2019 35
Trigger
November 2019 39
Usecasedocument
November 2019 32
Javaui
April 2020 32
Java
April 2020 21