Writing Trigger:
In many scenarios we write triggers on database tables. In one of my optimization task I found few triggers extremely slow in performance due to some conceptual lacking of the developer. So here I want to share my experience about those triggers.
Before writing a trigger we need to understand what trigger is and how exactly it works. 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 with 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:
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 row to be updated and then inserts the row with the new updated value. 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))
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 as well. 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. Only In that case he might write the same instructions to execute both if statements. This will in turn execute the same operation twice. 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.
So, while writing a trigger keep an eye on the following points:
i. If you write a single trigger for multiple event, be very careful to ensure that your trigger does not execute for unwanted events.
ii. When writing update trigger always check if your desired column is updated by using IF UPDATE(ColumnName).
iii. Be very careful in querying INSERTED and DELETED table.
iv. Try to avoid cursor from the trigger.
v. Ensure that your trigger is not creating any deadlock/Infinite loop on your database.
So, happy time while you write trigger for your application.
Thank you for sharing your experience about triggers. I think it will help all the developer. :-)
MD.ZAHIDUL ISLAM.NET Developer
Very good article. Thanks Mahmud
M. K. Basher
Software Developer (C#, .NET)
