<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Writing SQL Server Triggers Part 1:  After Triggers

Published on
17,037 Points
5,937 Views
11 Endorsements
Last Modified:
Awarded
Community Pick
Data Manipulation Language (DML) triggers are special types of stored procedures that operate on tables and views in a SQL Server database.  These triggers are invoked when data is inserted, updated, or deleted.  There are two potential scopes for these DML triggers; INSTEAD-OF the operation or AFTER the operation.  INSTEAD-OF triggers are typically used with views and happen (you guessed it) instead of the actual INSERT, UPDATE, or DELETE operation.  AFTER triggers, what we'll be focusing on today, occur after the operation has occurred.

It is worth noting that of this writing, there is no such thing as a “BEFORE trigger” in the SQL Server world.  It is my hopes that this is remedied in a future release.

When an operation occurs on a table with an AFTER trigger attach to it, such as an INSERT statement, the following actions occur:

1.      Constraints are checked to ensure that the INSERT does not violate any defined rules.
2.      The record is added to the table.
3.      The trigger is invoked.

The AFTER trigger always occurs AFTER the original DML statement occurs.  However, these triggers still execute in the context of the original transaction.  So, you can roll these transactions back in your AFTER trigger but only after all of the work has been done by the DML statement.

DML triggers have access to two special tables that store a copy of the data involved in the DML operation.  Below is an outline of these tables.

INSERTED - this pseudo table holds values for INSERT and UPDATE operations.  The structure of this table ALWAYS mimics the table where the data was modified.  For INSERT operations, this table holds the newly inserted values.  For UPDATE operations, this table holds the new set of values: the values that the field(s) were updated to.  

DELETED - this pseudo table holds values for UPDATE and DELETE operations.  The structure of this table ALWAYS mimics the table where the data was modified.  For UPDATE operations, this table holds the old set of values: the values that the columns in the table held BEFORE the update occurred.  For DELETE operations, this table holds the set of record(s) that were deleted.

Another MAJOR point with triggers is that the data you have access to in the trigger occurs at the BATCH level, and never EVER at the row level.  There is absolutely no built-in functionality in SQL Server to handle records in a trigger row-by-row.  To see how you can handle these situations, see my article here.  

So, lets take a look at how the INSERTED and DELETED tables are used when a DML statement occurs inside an AFTER trigger.  

First, I'll create a table and insert some data into it.
 
CREATE TABLE TestTable
(
	ID INT IDENTITY(1,1),
	ValueField INT DEFAULT(1),
	NewGuid UNIQUEIDENTIFIER DEFAULT(NEWID())
)
GO
INSERT INTO TestTable DEFAULT VALUES
GO 10
GO

Open in new window


Now I'll define an AFTER trigger on the TestTable table.
This trigger will be invoked whenever an INSERT, UPDATE, or DELETE occurs and simply outputs the values from the INSERTED and DELETED tables to the screen.
 
CREATE TRIGGER TR_TestTable_IUD
ON TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	SELECT 'INSERTED' AS TableName, * FROM INSERTED
	SELECT 'DELETED' AS TableName, * FROM DELETED
END
GO

Open in new window


Now that I have my trigger defined, I want to test it.
In the script below I'll insert a single value.  
 
INSERT INTO TestTable DEFAULT VALUES

Open in new window


My output from this statement is below.  
     Insert statementNotice that only the INSERTED table contains values.  This is due to the fact that for an INSERT operation, this table contains the newly inserted values into the table.

Now I'll delete a single record from the table.  
(Note:  the following syntax for the DELETE and UPDATE operations will only work for SQL Server 2005 and later.)
 
DELETE TOP(1) FROM TestTable
GO

Open in new window


The output from the DELETE statement is below.  
 Delete statementNotice this time that only the DELETED table contains data.

Finally, I'll run an UPDATE statement on the table to update a single record.  
 
UPDATE TOP(1) t
SET ValueField = ValueField + 1
FROM TestTable t

Open in new window


And, here's the output.  
 Update statementNotice this time that the INSERTED and DELETED tables both contain data.  The INSERTED table contains the NEW values; the DELETED table, the OLD values.

So, that's how you can define a basic SQL Server AFTER trigger.  To recap: we took a look at how you can define the trigger structure, how you can define the DML events that will invoke the trigger, and how the INSERTED and DELETE pseudo tables are populated based on the operation type.

Happy Coding!
Tim
11
Comment
Author:chapmandew
5 Comments
 

Administrative Comment

by:Kevin Cross
Tim:

Thank you for contributing another good piece of work to the Articles system and SQL community!

You have my YES vote above and it is my honor to announce that your article has been awarded the Bonus Points (Community Picks) and EE Approved medals.

Best regards,

mwvisa1
EE Page Editor
0
LVL 50

Expert Comment

by:DanRollins
Good article; got my Yes vote.

>> these triggers still execute in the context of the original transaction...
This is critical.  The triggered table is typically locked while the trigger proc is running, making it very unwise to do any sort of time-consuming operation with the trigger.  I tell students to take a quick action (such as insert a record into a "notification queue") and then exit the trigger ASAP.
0
LVL 60

Author Comment

by:chapmandew
Exactly.  The locks will still be held on the table (whether it be a row/page/extent/table lock) while the trigger is executing, definitely limiting your concurrency on your table.  
0

Expert Comment

by:TheCommunicator
Great article Tim. This is the first time I am reading article being published on EE. It was a fun ride. :).
0
LVL 67

Expert Comment

by:Jim Horn
Very good read.  Voted yes.
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month