Saturday, May 16, 2015

SQL 2005 - SQL Trigger dealing with multiple rows

1. SQL Triggers

A “Trigger” is a compiled unit of SQL server procedure, which can run automatically when an event occurs on the database objects. For example, you can write a piece of SQL Script (the trigger), which can be called whenever an insert (the event) happens on a specific table. There are different type of triggers available in SQL Server. The two important types are:

1) DDL Trigger: These triggers act on “Data Definition Language”; say for example a trigger procedure runs whenever a table is created. We can say DDL trigger as “Database level trigger” as it acts on the Database. The Trigger procedure can be invoked whenever the manipulation like Create, Alter and Drop takes place on database objects.

2) DML Trigger: These triggers act on “Data Manipulation Language”. In Database world, the tables maintain the Data. So manipulating data can be done through the tables by making use of Insert, Update and Delete statements on the table. So we can call DML trigger as "Table level trigger" since it operates at table level.

  1. DDL Triggers are at Database level and acts on DB Object (Table, views, procedure etc.) Manipulation (Create, Alter, Drop etc.)
  2. DML Triggers are at Table level and acts on Table data (Record) manipulation (Insert, Update, Delete)
The below screen shot shows both the triggers on Management studio:

Figure 1: Table Trigger, Database Trigger
Figure 1: Table Trigger, Database Trigger

In the above screen shot, Chief_NoDelete is a Table level trigger and the trigger fires when an attempt to delete a record on employee table is made. Similarly, StopViewDelete is a database level trigger, which will get fired whenever an attempt to drop a database view is made.

In this article, we will explore DML and DDL Triggers. You can use Microsoft supplied Pubs Sample database (Script available in SQL2005 Page) for all the code example.

2. DDL Trigger Example

Let us create a DDL trigger so that dropping view is prohibited. Have a look at the picture below:

Figure 2: Creating a DDL Trigger
Figure 2: Creating a DDL Trigger

In the code snippet, StopViewDelete is the Trigger Name and the trigger is created for "DROP_VIEW" event. The “ON DATABASE” states the trigger is at database level as the dropping a view has the impact at Database level. Execute the script, to create the trigger. Script code is given below:

-- 1. Simple DDL Trigger
--DDL trigger
   PRINT 'Contact Administrator to Drop view';

Once the database trigger StopViewDelete is created, the action of dropping a view is stopped with the indication that administrator needs to be contacted. The below SQL statement tries to delete the view:

--Attempt to delete a view
drop view titleview;

From the Messages tab of the SQL Server management studio, you can see result of executing the above statement. The screenshot of the message "Msg 3609, Level 14" is shown below:

Figure 3: Trigger Rollbacks the View Drop
Figure 3: Trigger Rollbacks the View Drop
Dropping a database level trigger is slightly different from dropping a normal trigger. To drop a database level trigger, place a keyword “ON Database” at the end of the trigger-drop statement.  The below script shows dropping a trigger that we created:

--Drop the Trigger. Note On Database keyword
Drop Trigger StopViewDelete On Database;

3. DML AFTER Trigger Example – On Deleting a Record

Now we will create a "DML Trigger", which will act at table level. From “Figure. 1” we can see that the trigger object is placed under the table node. Like Table constraints, Table index; DML Trigger is also table level object. The below picture shows a simple table level trigger:

Figure 4: DML Trigger After Delete
Figure 4: DML Trigger After Delete

In the above example, we are creating a trigger named “Chief_NoDelete” (Marked as 1). The purpose of this trigger is to avoid deletion of employee who has higher position and the positions are ‘Chief Executive Officer’, ‘Chief Financial Officer’. The ‘On Employee’ (Marked as 2) in the script shows that the trigger operates on the employee table. Once we specified table name, we need to specify triggering event and in our example we specified deleting a record as the triggering event. The “FOR Delete” (Marked as 3) keyword tells that the trigger will get fired when a delete statement is issued on the Employee table. Like Delete, one can create trigger for Insert as well as Update. This also tells that the trigger is “AFTER TRIGGER”. After triggers are fired once the DML statement is executed.

Once we specify the Trigger Event and Event Origin (In our case employee table), we can start writing the scripting code that defines how we want to respond to the occurrence of the event on the Employee table. In the above example, Job_Id of the record to be deleted is taken and stored in the @JobID local variable. When Job_Id is either 2 or 4 the employee is considered as chief and deletion of the record is skipped at trigger level. Note the Rollback statement after the message print. As this is an ‘AFTER TRIGGER’ and the deletion was already made, we have to rollback that. In the script you can see Job ID is taken from table called “Deleted”(Marked as 4). SQL Server maintains two virtual tables named “INSERTED”, “DELETED” and we will explore that in more detail in the later part of the article. For now you can see the job_id that is part of the record marked for deletion is supplied by the Deleted virtual table. Now, when we will try to delete a chief employee by executing the below SQL statement:

Delete from Employee where lname = 'Chang';

The employee we are attempting to delete is a chief employee and this is shown in the below screen shot:

Figure 5: Chief Employees
Figure 5: Chief Employees

OK, as the employee is ‘Chief Financial Officer’ the attempt to delete the record is stopped at trigger level by making use the rollback statement. Have a look at the message below:

Chief Employee Can not be Deleted
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

The message states that the deletion is cancelled at the trigger ‘Chief_NoDelete’. Note that in SQL server, triggers are invoked per statement. In our case the delete statement on employee table fired the trigger, and in the trigger body the rollback statement considers that delete statement as the transaction and reverts that back to the original state.

The trigger we wrote won’t work for delete statement that affects multiple records. We will see that in detail towards the end of the article.

4. INSERTED and DELETED virtual tables

In the previous example we saw a table called deleted. SQL Server maintains two virtual tables named “INSERTED” and “DELETED”. When there exists a trigger for the table say <ABC>, SQL server populates the virtual table while executing the DML statements on that table <ABC>. Have a look at the below picture:

  Figure 6: Inserted and deleted Virtual table entries
Figure 6: Inserted and deleted Virtual table entries

The first table in the left shows that inserted virtual table having valid entries. This is because the insert statement newly introduces values for the database table. Similarly, the table in the middle shows Virtual table entries for the Delete statement and here we see Deleted virtual table has entries and insert is empty. In case of update (The right most one), for a particular column in record, we may end up value before the update (Deleted) and value after the update (Inserted) and that is why we have both ‘Inserted’ and ‘updated’ virtual table gets populated.

5. Instead of Trigger – For Delete

Previously we saw “AFTER TRIGGER”, which get fired once the execution of DML statement is finished. OK. The “INSTEAD OF TRIGGER” replaces the actual DML statement that fired the trigger. Confusing? For example, say table ABC has an instead of trigger in it, executing the Insert statement on the table ABC is replaced by the Trigger. That is ‘InStead of’ actual insert statement, the trigger get fired. Got it right?

Below is the example for “Instead of Trigger” on Delete statement:

Figure 7: Instead of Trigger
Figure 7: Instead of Trigger

The Instead of trigger is created the same way  after trigger got created. The only difference is in the syntax which uses the keyword “Instead of” (Marked as 1) replacing the “For”.  As the trigger replaces the Delete statement, we took employee id value from the ‘deleted’ virtual table (Marked as 2) and fired the delete statement again by introducing that value in the where clause(Marked as 3). As in the previous “AFTER TRIGGER” example, the “INSTEAD OF” trigger example also works for a delete operation that affects single row. Now execute the below statement which tries to delete a single record:

Delete from Employee where lname = 'Chang';

The result of executing the above statement is shown in the below screen shot:

Figure 8: Result of Delete statement
Figure 8: Result of Delete statement

First you see here a message printed, stating that the ‘Instead-of trigger fired’ (Marked as 1), which comes from the trigger Chief_Delete_Instead. The message marked as 2 is coming from the trigger ‘After Trigger’ as we are trying to delete the chief employee. The error message marked as 3 in the above picture, states that delete statement is executed from the “Instead-of” trigger at line 7. Since the employee is chief employee, the ‘After trigger’ rollbacks the delete statement invoked by the ‘Instead of’ trigger (Marked as 4).

In Summary (In the perspective delete statement we attempted from the management studio),

  1. Employee table has two triggers. One is a “Instead of Trigger” (Chief_Delete_Instead) and other one is “After Trigger” (Chief_NoDelete)
  2. When we execute the Delete statement, after making the virtual table entries, Instead of the actual Delete statement, the trigger Chief_Delete_Instead is invoked
  3. After printing Message (Just for Demo), It invokes the delete statement (SQL Server avoids the recursion here)
  4. Now, the after trigger is fired and roles back the transaction made by the Instead of trigger.

To add more explanation to the above summary, have a look at the below picture to understand execution order:

Figure 9: Trigger Execution order when both AFTER & INSTEAD OF Exists
Figure 9: Trigger Execution order when both AFTER & INSTEAD OF Exists

The DML statement that we want to execute enters SQL Server engine (Marked as 1). After making the execution plan, when the Table involved in DML statement has any triggers, the corresponding INSERTED, DELETED virtual table entries are made (Marked as 2). After making the virtual table entries the Instead of Trigger gets fired (Marked as 3). Note that the trigger is executed instead of actual DML statement so it can execute any DML Statement (Shown as 4). In our example, we are executing the delete statement again by using emp_id (The Actual delete is based on Employee last name) in where clause.  In our case, the Employee table Trigger, ‘After Delete’ is executed as the ‘Instead of’ trigger executes the DML statement (Shown as 5).

6. Trigger Handling Multi Row

First let us delete both the triggers on the employee table. Execute the below SQL statements to drop the triggers:

Drop Trigger Chief_NoDelete;
Drop Trigger Chief_Delete_instead;

To handle mutiple rows we should use cursor on the virtual tables. The new version Chief_NoDelete “Instead of TRIGGER” that handles multiple rows is shown in the below picture:

Figure 10: Trigger handling multiple rows affected
Figure 10: Trigger handling multiple rows affected

First the trigger is named and created on the Employee table for the Delete operation (Marked as 1).  After the trigger creation syntax, a “CURSOR” on the DELETED virtual table is declared and opened (Marked as 2). The cursor queries only Employee ID and Job Id from the ‘DELETED’ virtual table. Note that we are writing Trigger body for the Delete trigger & if it is an insert trigger then we should have used the ‘INSERTED’ virtual table. The fetch statement on the cursor retrieves columns from current row and assigns that into local variable. After fetch, the cursor pointer advances to next row in the cursor. The validity of the fetch on the current row is tested using “@@Fetch_Status” (marked as 3). The fetch will be successful when @@Fetch_Status contains 0. Based on the Fetch status we are forming iteration over the effected rows (DELETED Virtual table). Inside the iteration body, we are checking whether the current row belongs to ‘Chief Employee’ by testing the job id fetched from Cursor (Marked as 4).  Based on the test we are either discarding the deletion or allowing it. At the end of the iteration we are fetching the new row values into local variables (Marked as 5). Finally the cursor is closed and memory is cleared (Marked as 6).

Now Let us execute the delete statement, which affects more than one row. The delete statement is shown below:

Delete from Employee where job_lvl > 150;

Executing the above statement now produces the following result in the output window:

Figure 11: Result of Executing the Trigger
Figure 11: Result of Executing the Trigger

The trigger skips deleting the chief employee and its corresponding message is marked as 1 (Sample result). A successful deletion is marked as 2. Note that the InStead of trigger fires instead of the DML statement. So the trigger body should decide about the DML statement on which it is fired.  As we are experimenting the Delete record on the Employee table, below statement will be useful to restore the records:

insert employee values ('PTC11962M', 'Philip', 'T', 'Cramer', 2, 215, '9952', '11/11/89')
insert employee values ('AMD15433F', 'Ann', 'M', 'Devon', 3, 200, '9952', '07/16/91')
insert employee values ('F-C16315M', 'Francisco', '', 'Chang', 4, 227, '9952', '11/03/90')
insert employee values ('LAL21447M', 'Laurence', 'A', 'Lebihan', 5, 175, '0736', '06/03/90')
insert employee values ('PXH22250M', 'Paul', 'X', 'Henriot', 5, 159, '0877', '08/19/93')
insert employee values ('SKO22412M', 'Sven', 'K', 'Ottlieb', 5, 150, '1389', '04/05/91')
insert employee values ('RBM23061F', 'Rita', 'B', 'Muller', 5, 198, '1622', '10/09/93')
insert employee values ('MJP25939M', 'Maria', 'J', 'Pontes', 5, 246, '1756', '03/01/89')
insert employee values ('JYL26161F', 'Janine', 'Y', 'Labrune', 5, 172, '9901', '05/26/91')
insert employee values ('CFH28514M', 'Carlos', 'F', 'Hernadez', 5, 211, '9999', '04/21/89')
insert employee values ('VPA30890F', 'Victoria', 'P', 'Ashworth', 6, 140, '0877', '09/13/90')
insert employee values ('L-B31947F', 'Lesley', '', 'Brown', 7, 120, '0877', '02/13/91')
insert employee values ('ARD36773F', 'Anabela', 'R', 'Domingues', 8, 100, '0877', '01/27/93')
insert employee values ('M-R38834F', 'Martine', '', 'Rance', 9, 75, '0877', '02/05/92')
insert employee values ('PHF38899M', 'Peter', 'H', 'Franken', 10, 75, '0877', '05/17/92')
insert employee values ('DBT39435M', 'Daniel', 'B', 'Tonini', 11, 75, '0877', '01/01/90')
insert employee values ('H-B39728F', 'Helen', '', 'Bennett', 12, 35, '0877', '09/21/89')
insert employee values ('PMA42628M', 'Paolo', 'M', 'Accorti', 13, 35, '0877', '08/27/92')
insert employee values ('ENL44273F', 'Elizabeth', 'N', 'Lincoln', 14, 35, '0877', '07/24/90')
insert employee values ('MGK44605M', 'Matti', 'G', 'Karttunen', 6, 220, '0736', '05/01/94')
insert employee values ('PDI47470M', 'Palle', 'D', 'Ibsen', 7, 195, '0736', '05/09/93')
insert employee values ('MMS49649F', 'Mary', 'M', 'Saveley', 8, 175, '0736', '06/29/93')
insert employee values ('GHT50241M', 'Gary', 'H', 'Thomas', 9, 170, '0736', '08/09/88')
insert employee values ('MFS52347M', 'Martin', 'F', 'Sommer', 10, 165, '0736', '04/13/90')
insert employee values ('R-M53550M', 'Roland', '', 'Mendel', 11, 150, '0736', '09/05/91')
insert employee values ('HAS54740M', 'Howard', 'A', 'Snyder', 12, 100, '0736', '11/19/88')
insert employee values ('TPO55093M', 'Timothy', 'P', 'O''Rourke', 13, 100, '0736', '06/19/88')
insert employee values ('KFJ64308F', 'Karin', 'F', 'Josephs', 14, 100, '0736', '10/17/92')
insert employee values ('DWR65030M', 'Diego', 'W', 'Roel', 6, 192, '1389', '12/16/91')
insert employee values ('M-L67958F', 'Maria', '', 'Larsson', 7, 135, '1389', '03/27/92')
insert employee values ('PSP68661F', 'Paula', 'S', 'Parente', 8, 125, '1389', '01/19/94')
insert employee values ('MAS70474F', 'Margaret', 'A', 'Smith', 9, 78, '1389', '09/29/88')
insert employee values ('A-C71970F', 'Aria', '', 'Cruz', 10, 87, '1389', '10/26/91')
insert employee values ('MAP77183M', 'Miguel', 'A', 'Paolino', 11, 112, '1389', '12/07/92')
insert employee values ('Y-L77953M', 'Yoshi', '', 'Latimer', 12, 32, '1389', '06/11/89')
insert employee values ('CGS88322F', 'Carine', 'G', 'Schmitt', 13, 64, '1389', '07/07/92')
insert employee values ('PSA89086M', 'Pedro', 'S', 'Afonso', 14, 89, '1389', '12/24/90')
insert employee values ('A-R89858F', 'Annette', '', 'Roulet', 6, 152, '9999', '02/21/90')
insert employee values ('HAN90777M', 'Helvetius', 'A', 'Nagy', 7, 120, '9999', '03/19/93')
insert employee values ('M-P91209M', 'Manuel', '', 'Pereira', 8, 101, '9999', '01/09/89')
insert employee values ('KJJ92907F', 'Karla', 'J', 'Jablonski', 9, 170, '9999', '03/11/94')
insert employee values ('POK93028M', 'Pirkko', 'O', 'Koskitalo', 10, 80, '9999', '11/29/93')
insert employee values ('PCM98509F', 'Patricia', 'C', 'McKenna', 11, 150, '9999', '08/01/89')


-- 1. Simple DDL Trigger
--DDL trigger
   PRINT 'Contact Administrator to Drop view';

--2. Simple After Trigger

--Trigger Chief_NoDelete
--Action: After Deleting a Row
Create Trigger Chief_NoDelete
On Employee For Delete As
            Declare @JobID as smallint;
            Select @JobID = job_id from Deleted;
            if (@JobID = 2 or @JobID = 4)
                        Print 'Chief Employee Can not be Deleted';

--3. Simple Instead Of Trigger

--Trigger: Chief-Delete

--Action: Called In Place of Deleting a Row
Create Trigger Chief_Delete_instead
On Employee Instead of Delete As
            Declare @EmpID as char(9);
            Select @EmpID = emp_id from Deleted;
            Print 'Instead of Trigger Fired';
            Delete from Employee where emp_id = @EmpID;

--4. InStead Of Trigger dealing Batch Delete (Multiple Rows)

--Trigger: Chief_NoDelete

--Action: After Deleting a Row Process all the rows
Create Trigger Chief_NoDelete
On Employee Instead of Delete As
            -- Declaration of variable to hold data from DELETED
            -- virtual table
            Declare @emp_id char(9);
            Declare @job_id smallint;

            -- Declare cursor for the DELETED Table & Open it
            Declare Deleted_cursor CURSOR for
            Select emp_id, job_id from DELETED;
            Open Deleted_cursor;

            -- Attempt to fetch first record and loop next
            FETCH NEXT from Deleted_cursor into @emp_id, @job_id;
            While @@Fetch_Status = 0
                        if (@job_id = 2 or @job_id = 4)
                                                PRINT 'Chief Employee delete Ignored';
                                                PRINT '  Delete Skipped For ' + @emp_id;
                                                Delete from Employee where Emp_Id = @emp_id;
                                                PRINT 'Deleted Employee: ' + @emp_id;
            FETCH NEXT from Deleted_cursor into @emp_id, @job_id;
            CLOSE Deleted_cursor;
            DEALLOCATE Deleted_cursor;

Like this site? Tell it to your Firend :)