1. Introduction To SQL Triggers

An ‘SQL 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) takes place on a specific table. There are various types of triggers possible in SQL Server. The two crucial types are:

• DDL Triggers: 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 an ‘Database level trigger’ hence it acts on the DB. SQL Server invokes the Trigger procedure whenever the manipulation like Create, Alter and Drop takes place on its DB objects.
• DML Triggers: These triggers act on ‘Data Manipulation Language’. In DB world, the tables maintain the Data. So, we can do manipulating data through the tables by making use of Insert, Update and Delete statements on the table. We can call DML trigger as an ‘Table Level Trigger’ since it operates at table level. Simply,
  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).}

Triggers We will Create in This Example

The below screen shot shows both the triggers on Management studio(Note, we are going to create these triggers):

In the above screenshot, Chief_NoDelete is a Table level trigger and then it fires when an attempt to delete a record on employee table is made. Similarly, StopViewDelete is a DB level trigger, which will get fired whenever an attempt to drop a DB View is made. Note, we will create these triggers in the coming sections of this example.

In this article, we will explore DML and DDL Triggers. You can use Microsoft supplied Pubs Sample database for all the code example. Alternatively, you can create your own table and proceed.

2. Creating Database Level Triggers (DDL Triggers)

Let us create a DDL trigger so that we can stop dropping a view from the DB. Have a look at the picture below:

In the code snippet, StopViewDelete is the Trigger Name and we created it for DROP_VIEW event. The ON DATABASE states the trigger is at database level as dropping a view has the impact at DB Objects level. Now you can execute the above script, to create the trigger. Script code is below:

After creating the DB trigger StopViewDelete, we stop dropping a view from the DB with the hint that admin needs to be contacted. The below SQL statement tries to delete the view:

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

Dropping a database level trigger differs slightly from dropping a normal trigger. To drop a database level trigger, we should place a keyword ON Database at the end of the trigger-drop statement.  The below script shows dropping a trigger we created:

3. Table Level Triggers (DML Triggers)

Now we will create the ‘DML Trigger’, which will act at table level. From below picture in the Introduction section, we can see that the trigger object is placed under the table node. Like Table constraints, Table index, the DML Trigger is also a table level object. The below picture shows a simple table level trigger:

3.1 Code Explanation – Trigger Name & Event

In the above example, we are creating a trigger named Chief_NoDelete (Marked as 1). This trigger avoids deletion of an 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 point out the triggering event.

In our example, we specified deleting a record as the triggering event. The FOR Delete (Marked as 3) keyword tells SQL Server that this trigger will get fired when a delete statement is issued on the Employee Table. This also tells that the trigger is an ‘AFTER TRIGGER’. SQL Server invokes the After Triggers after executing the DML statement on the DB. Like Delete, one can create a trigger for Insert and Update just by replacing the keyword delete at marker position 3.

3.2 Code Explanation – Trigger Body

Once we specify the Trigger Event and Event Origin (In our case employee table), we can write the scripting code that defines how we want to respond to the event occurrence on the Employee table. In the above example, we take the Job_Id of the record to be deleted and stored it in the @JobID local variable. When Job_Id is 2 or 4 the employee is a chief employee and we skip deleting the record at trigger level. You can note the Rollback statement after the message print.

As this is an ‘AFTER TRIGGER’ and SQL Server already made the deletion. Hence, we have to rollback that. In the script you can see we take Job ID from a table called ‘Deleted’ (Marked as 4). SQL Server maintains two virtual tables named INSERTED, DELETED. 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.

3.3 Testing the Trigger – Attempt to Delete Chief Employee

Now, we will try to delete a chief employee by executing the below SQL statement:

The employee we are attempting to delete is a chief employee and below screenshot shows that:

As the employee is ‘Chief Financial Officer’, the Trigger stops the attempt to delete the record by making use of 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 ruled out at the trigger ‘Chief_NoDelete’. Note that in SQL server, it applies triggers per statement. In our case the delete statement on employee table fired the trigger, and in the trigger body the rollback statement deals with the delete statement as a transaction and reverts that back to the original state.

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

4. INSERTED & DELETED Virtual Tables

In the previous example we saw a table called DELETED, which we used in the body of the SQL Trigger. SQL Server maintains two virtual tables named INSERTED and DELETED. When there is 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:

The first table on the left shows that INSERTED virtual table having valid entries. This is because the insert statement newly includes 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 rightmost one), for a specific column in a record, we may end up with a value before the update (Deleted) and value after the update (Inserted) and that is why we have both INSERTED and DELETED virtual table gets filled.

5. ‘Instead of Trigger’ – For Delete

Previously we saw AFTER TRIGGER, which gets fired after executing the DML statement in the DB. The INSTEAD OF TRIGGER replaces the actual DML statement that fired the trigger. Is it confusing? For example, say table ABC has an ‘Instead Of Trigger for Insert’ in it. Now, executing the Insert statement on the table ABC is replaced by the Trigger. That is instead of the actual insert statement the trigger gets fired. Hence, we call these trigger as a ‘Instead Of’ Trigger.

5.1 Instead Of Trigger SQL Example

Below is the example for ‘Instead Of Trigger’ on Delete statement:

We create the ‘Instead Of Trigger’ the same way how we created the After Trigger in the past section. The only change 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 setting up that value in the where clause (Marked as 3). As in the early AFTER TRIGGER example, the INSTEAD OF Trigger example also works for a delete operation that affects single row.

5.2 Try to Delete Chief Employee

Now we will execute the below statement which tries to delete a single record:

The below Screenshot shows the result of executing the above statement:

First, we see here a message printed, stating that ‘Instead-of trigger fired’ (Marked as 1), which comes from the body of the Chief_Delete_Instead  Trigger. 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).

5.3 Trigger Execution Sequence – A Quick Summary

In Summary (In the perspective of the 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 in SQL Server query window, SQL Server makes the virtual table entries, and then fires Instead of Trigger Chief_Delete_Instead. This trigger replaces the delete statement and hence SQL Server does not execute the delete statement in the query window directly on the DB.
3. After printing a Message in the body of the Chief_Delete_Instead, SQL Server invokes the delete statement from the body of the Trigger. Note, it avoids the recursion here.
4. Finally, SQL Server Fires the Chief_NoDelete After Trigger and roles back the delete action made by the Instead of trigger Chief_Delete_Instead.

5.4 Trigger Execution Order Further Explained

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

The DML statement that we want to execute enters SQL Server engine (Marked as 1). The SQL Server, after making the ‘Execution Plan’, checks the Table involved in DML statement is hooked to any triggers. If so, it fills the corresponding INSERTED, DELETED virtual table entries (Marked as 2).

After making the virtual table entries, it fires the Instead of Trigger (Marked as 3) if it exists. Note, SQL Server runs the ‘Instead Of Trigger’ instead of the actual DML statement. If the same DML action (Say a Delete statement in the Instead of Trigger Body for the Delete) is invoked from the body of the Instead Of Trigger (Shown as 4), SQL Server looks for After Trigger for the DML Statement as it is already inside the ‘Instead of Trigger’ body.

In our example, we are running the delete statement again by using emp_id in the trigger body. Note, the actual delete we attempted in the SQL Server Management studio was based on Employee’s last name.  In our case, SQL Server fires the ‘After Delete’ Trigger of the Employee table as the ‘Instead of’ trigger has the Delete DML statement (Shown as 5).

6. Multi-Row Trigger Via SQL Cursors

First, let us delete both the triggers on the employee table. Below two SQL Statements drop those triggers:

To handle more than one row, 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:

6.1 T-SQL Code Explanation

First we name the trigger and create it 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 was an insert trigger, then we should have been using the INSERTED virtual table.

The Fetch Next  statement on the cursor pulls columns from current row and assigns that into local variable. After fetch, the Cursor Pointer advances to next row in the DELETED virtual table. The validity of the fetch on the current row is tested using @@Fetch_Status (marked as 3). We can say the fetch was valid when @@Fetch_Status contains 0. Based on the fetch status we are forming iteration over the affected rows on the 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 from the DELETED virtual table into local variables (Marked as 5). The next iteration uses these fetched values. Finally, we close the cursor and clear the memory (Marked as 6).

6.2 Testing The Multi-Row Trigger

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

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

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 with the Delete record on the Employee table, below statement will be useful to restore the records:

7. Code Listings

7.1 DDL Trigger Example

7.2 SQL After Delete Trigger Example

7.3 SQL ‘Instead Of’ Trigger Example – Single Row

7.4 SQL ‘Instead Of’ Trigger Example – Multiple Rows

No Downloadable Source Code For this Example (Refer above Listings)

• Spawning A Process in C#
• Creating Custom Performance Counter in C#

Categories: SQL Server

Tags: DDL Triggers, DELETED Virtual Table, DML Triggers, INSERTED Virtual Table, Instead Of Trigger, SQL Cursors

This site uses Akismet to reduce spam. Learn how your comment data is processed.