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 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:

i. All constraints are enforced.
ii. All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
iii. The inserted and deleted tables are created in memory for use within the trigger.
iv. The triggering action (in this case the UPDATE statement) is executed.
v. 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 delete the rows to be updated and keep 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 and 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 in to the table.

Now note carefully the mistakes that the developer did 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 statement will be successful for update operations! So, if the developers want some code to execute for all the operations he might do the mistake of writing the same code in both 1st and 2nd if block. 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 table 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!