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
. Now, the question is how the database handles the execution of a trigger when it fires?
If you write a trigger for
operation on a table, after firing the trigger, it creates a table named “
” in the memory. Then it performs the
operation and after that the statements inside the trigger executes. We can query the “
” table to manipulate or use the inserted row/s from the trigger.
Similarly, if you write a trigger for
operation on a table, it creates a table in memory named “
” and then deletes the row.
More importantly, you must understand how an
trigger works. After firing an
trigger, it works in the following sequence:
- All constraints are enforced.
- All declarative referential integrity (DRI) constraints are enforced (via foreign keys).
- The inserted and deleted tables are created in memory for use within the trigger.
- The triggering action (in this case the
UPDATE statement) is executed.
- The AFTER UPDATE trigger executes.
From the above steps, you can see that no table called “
” is created. Actually on database no operation called
executes. Internally it actually deletes the rows to be updated and keeps the deleted rows in
table. The updated rows that are sent to update the table are kept in
TABLE. After the old rows are backed up to the
table, updated rows from
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
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
if(exists(select Contact_Id from inserted where Contact_id is not null))
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
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
. Now if
is a not
column of the table, we will never get
table. So, here even though
is not updated, the operation of the triggers will execute.
It is not finished yet. It has more problems. The developer wrote the 2nd
condition assuming that
table will be created only when trigger fires for any
operation on the table. But you see, as a matter of fact, this table is also available when the trigger fires for
The situation will be the worst if the developer thinks the first
statement will be successful for
operation and 2nd
statement will be successful for
operation. Note that, in reality both 1st and 2nd
statements will be successful for
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
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.
Say you are updating 5 rows of a table with a single
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
table all the time. You may think internally SQL Server creates separated
tables for each of the 5 rows. But no! In reality, it will create 1
table and 1
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:
- If you write a single trigger for multiple event, be very careful to ensure that your trigger does not execute for unwanted events.
- When writing
update trigger, always check if your desired column is updated by using
- Be very careful in querying
- Try to avoid cursor from the trigger.
- 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!