Trigger

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