Skip to content

SQL DML Triggers

17 December 2008

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
AS
    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
AS
BEGIN
    INSERT INTO Example_Table_A (ID, A_Column)

        SELECT ID, A_Column
            FROM inserted
    INSERT INTO Example_Table_B (ID, B_Column)
        SELECT ID, B_Column
            FROM inserted
END

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
AFTER UPDATE
AS
    UPDATE tr_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.

From → code complete

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: