SQL DML Triggers
In this post I will talk about DML (Data Manipulation Language) triggers. There is another kind of triggers, DDL (Data Definition Language) triggers, but I won’t cover them here. As always, you can get more detailed information on MSDN.
SQL Server triggers are stored procedures that execute when either an Insert, an Update or a Delete takes place on a table or a view. Triggers can fire before the actual action takes place (FOR triggers), instead of the action (INSTEAD OF triggers) or after the action (AFTER trigger). For exemple, on a table Example_Table, a trigger can be set up as
CREATE TRIGGER tr_Example_Table_IUD ON Example_Table
FOR INSERT, UPDATE, DELETE
PRINT 'Trigger fired'
Now whenever one of the actions is applied on the table, SQL Server will output ‘Trigger fired’. Whenever a trigger is fiered, two temporary tables are made available: inserted and deleted. The inserted table contains all rows that will/are/were added to the table and the deleted table contains all rows that will/are/were removed from the table. An explicit update table doesn’t exist because an update is considered to be a delete followed by an insert, so the deleted table contains the old rows and the inserted table contains the modified rows.
An great use of triggers is to enable insert/update/delete capabilities on a view. If we have, for example, a view that joins two tables – Example_View, which is setup as
SELECT a.ID, a.A_Column, b.B_Column FROM Example_Table_A a JOIN Example_Table_B b ON a.ID = b.ID, we won’t be able to insert data into it directly. But this can be done with a trigger.
CREATE TRIGGER tr_Example_View_I ON Example_View
INSTEAD OF INSERT
INSERT INTO Example_Table_A (ID, A_Column)
SELECT ID, A_Column
INSERT INTO Example_Table_B (ID, B_Column)
SELECT ID, B_Column
Another example would be always storing the last modified time for every row of a table. If table Example_Table has an ID primary key and a Date_Modified column, the following trigger can be set:
CREATE TRIGGER tr_Example_Table_U ON Example_Table
SET Date_Modified = getdate()
WHERE ID IN (SELECT ID FROM inserted)
Note that no modifications can be applied on the inserted and deleted tables themselves, so the update cannot be made on the inserted table in a FOR trigger.