Be Very Careful When You Write SQL Trigger

In many scenarios, we write triggers on database tables. Before writing a trigger, we need to understand what trigger is and how exactly it works. Because, lack of clear knowledge on triggers can ake your life difficult. Trigger is actually a procedure that runs in response of an event fired due to performing some operations on database tables. The events could be insert, update or delete. Now, the question is how the database handles the execution of a trigger when it fires?

If you write a trigger for insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in the memory. Then it performs the insert operation and after that the statements inside the trigger executes. We can query the “INSERTED” table to manipulate or use the inserted row/s from the trigger.

Similarly, if you write a trigger for delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.

More importantly, you must understand how an update trigger works. After firing an update trigger, it works in the following sequence:

1. All constraints are enforced.
   
2. All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
   
3. The inserted and deleted tables are created in memory for use within the trigger.
   
4. The triggering action (in this case the UPDATE statement) is executed.
   
5. The AFTER UPDATE trigger executes.
   

From the above steps, you can see that no table called “UPDATED” is created. Actually on database no operation called update executes. Internally it actually deletes the rows to be updated and keeps the deleted rows in DELETED table. The updated rows that are sent to update the table are kept in INSERTED TABLE. After the old rows are backed up to the DELETED table, updated rows from INSERTED tables get inserted into the targeted table.

So, from an update trigger, we can access both INSERTED</code and <code>DELETED table though directly we may not execute any insert or delete operation. This is a very important concept for us. Here I am providing the mistakes you may make if you are not clear on this.

Consider the following trigger:

CREATE TRIGGER TriggerName ON YourTableName
AFTER UPDATE ,INSERT , DELETE
AS
if(exists(select Contact_Id from inserted where Contact_id is not null))
begin
--Do your operation
end
if(exists(select Contact_Id from deleted where Contact_id is not null))
begin
--Do your operation
End

Here the developer wrote the trigger for all the events on the table and expecting to do some operation if Contact_ID is inserted, deleted or updated into the table.

Now note carefully the mistakes that the developer made in this trigger. For example, an operation is executed on the table which updates some other field other than Contact_ID. Now if Contact_ID is a not null column of the table, we will never get null from INSERTED and the DELETED table. So, here even though Contact_ID is not updated, the operation of the triggers will execute.

It is not finished yet. It has more problems. The developer wrote the 2nd if condition assuming that DELETED table will be created only when trigger fires for any Delete operation on the table. But you see, as a matter of fact, this table is also available when the trigger fires for update operation.

The situation will be the worst if the developer thinks the first if statement will be successful for INSERT and UPDATE operation and 2nd if statement will be successful for DELETE operation. Note that, in reality both 1st and 2nd if statements will be successful for update operations! So, if a developer wants some code to execute for all the operations, he might make the mistake of writing the same code in both 1st and 2nd if blocks. This will in turn execute the same operation twice while any update operation is done. Doesn’t it sound very silly and surprising? Yes. But if you are not careful, these mistakes can happen anytime and can take you to hell from the heaven you are currently in.

Another Mistake:

Say you are updating 5 rows of a table with a single update statement. Then, in trigger, you are querying as follows:

DECLARE @IsPublished [bit] 
SET @IsPublished = (SELECT [IsPublished] FROM [inserted])

Here, you are expecting 1 row in the INSERTED table all the time. You may think internally SQL Server creates separated INSERTED AND DELETED tables for each of the 5 rows. But no! In reality, it will create 1 INSERTED table and 1 DELETED table. Each of the tables will contain all the 5 rows. So, the above SQL statement will return an error. You should always be careful of this.

So, while writing a trigger, keep an eye on the following points:

1. If you write a single trigger for multiple event, be very careful to ensure that your trigger does not execute for unwanted events.
   
2. When writing update trigger, always check if your desired column is updated by using IF UPDATE(ColumnName).
   
3. Be very careful in querying INSERTED and DELETED table.
   
4. Try to avoid cursor from the trigger.
   
5. Ensure that your trigger is not creating any deadlock/Infinite loop on your database.
   

Wish you write error free triggers and save hours of trouble shooting!