Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to DML Triggers

By Jack Corbett,

Being a frequent contributor to the forums, I have seen many questions about triggers and seen many trigger implementations that could be dangerous. Hopefully this article will answer some of those questions and clear up some common misconceptions.

Types of Triggers

SQL Server provides 2 types of DML triggers, AFTER and INSTEAD OF (added in SQL Server 2000). Triggers are only fired when a data modification statement is issued (UPDATE, INSERT, DELETE). There are no SELECT triggers. SQL Server 2005 also intoduced DDL Triggers which will not be dealt with in this article.

AFTER Triggers

AFTER triggers take place after the action has taken place and are the default trigger created if you do not specify the trigger type in the CREATE TRIGGER statement. When an AFTER trigger is applied to a table the action takes place, then the trigger fires. Because the trigger is part of the transaction any error withing the trigger will cause the entire transaction to fail and rollback. A typical use for an AFTER trigger is to log the action to an audit or logging table.

INSTEAD OF Triggers

INSTEAD OF triggers take place instead of the modification being made. They are Microsoft's answer to BEFORE triggers available in other RDBMS systems. If I define an INSTEAD OF trigger on a table FOR INSERT the trigger will fire BEFORE the data is inserted into the table. If I do not repeat the INSERT within the trigger then the insert will not take place. For example this trigger would be inappropriate as it never completes the insert:

CREATE TRIGGER HumanResources.Department_Instead_Of_Ins
ON HumanResources.Department
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
; -- Insert statements for trigger here
IF Exists(Select Count(DepartmentId)
From inserted I Join
HumanResources.Department D On
I.GroupName = D.GroupName
Group By GroupName Having Count(DepartmentId) >= 5)
Begin
Insert Into
dbo.ErrorLog
(
ErrorTime,
UserName,
ErrorNumber
ErrorMessage
)
Select
GetDate(),
Suser_Name(),
52999,
GroupName + ' Already full of departments'
From
inserted
End
END

If your business rules only allow for five departments in a group, you could do something like this so the insert takes place:

ALTER TRIGGER HumanResources.Department_Instead_Of_Ins
ON HumanResources.Department
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
;

-- Insert statements for trigger here
IF Exists(Select Count(I.DepartmentId)
From inserted I Join
HumanResources.Department D On
I.GroupName = D.GroupName
Group By I.GroupName Having Count(I.DepartmentId) >= 5)
Begin
Insert Into
dbo.ErrorLog
(
ErrorTime,
UserName,
ErrorNumber,
ErrorMessage
)
Select
GetDate(),
Suser_Name(),
52999,
GroupName + ' Already full of departments'
From
inserted

RAISERROR('Insert failed as Group already full of departments', 16,1)
End
Else
Begin
Insert Into
HumanResources.Department
(
Name,
GroupName,
ModifiedDate
)
Select
Name,
GroupName,
ModifiedDate
From
inserted
End
END

Coding Triggers

All of the following examples will use AFTER triggers.

Coding to Handle Sets

The first thing a DBA or Developer must understand is that triggers deal with SETS, not individual rows. The most common mistake I see made in triggers is using variables incorrectly within a trigger, which means the trigger will only handle a single row update. An example would be this:

Create Trigger Person.Contact_Update
On Person.Contact
AFTER Update
AS Declare
@ContactId Select
@ContactId = ContactId
From
inserted IF @ContactID > 10
BEGIN
Insert Into
Person.ContactLog
(
ContactID,
Action
)
Values
(
@ContactID,
'UPDATE'
)
END

The trigger handles this update correctly:

Update Person.Contact
     Set LastName = 'Corbett'
Where
     ContactId = 12

But it does not handle this update correctly:

Update Person.Contact
     Set LastName = 'Corbett'
Where 
	ContactId Between 12 and 17

Which row update will be logged by the trigger? Your guess is as good as mine since you cannot use the ORDER BY clause in an UPDATE, thus the order of the update is not guaranteed. Here is the correct way to code this trigger to handle any UPDATE:

Create Trigger Person.Contact_Update
On Person.Contact
AFTER Update
AS IF Exists
(Select * From inserted Where ContactId > 10)
BEGIN
Insert Into
Person.ContactLog
(
ContactID,
Action
)
Select ContactID 'Update' From inserted Where ContactId > 12
END

Now, I know someone is asking, "What if I need to process each row?". If this were posted on a forum I would ask, why? What is your desired result at the end of the process? Then I would attempt to provide a set-based option, and if I couldn't I would bet someone else could. In all honesty, if you think you need a loop (cursor) in a trigger you probably want to re-evaluate your process as that will absolutely kill performance. In my last position I worked with a third party product that handled our quality results, both from production machine processes and the lab. We used industry-standard unique keys for our products which would be re-used every 2 years, so I needed to purge data prior to the the re-use of the unique key. Since the product tied to the test results was no longer in the system I also wanted to purge the quality results, but I ended up never getting the process done. Why? Because the system had a trigger on the tests table with a cursor in it! So when I would attempt a batch delete of 1 day's data, about 10000 or so results, I would wait and wait and wait, while the server was pegged at 100%. Thankfully I had a test server, and never tried this on the production server. As I investigated why the server was being pounded by a simple delete of ~10000 rows using an index, I found the trigger with the cursor. Since it was a 24 x 7 x 365 operation I had no downtime to do this delete, so I contacted the vendor with the problem and provided a set-based trigger that did what they needed. When I tested using the set-based trigger it took minutes to do the delete with minimal server impact, but the vendor wasn't interested in fixing the problem. I wish I still had access to the database so I could give you accurate numbers regarding the improvement, but I can say it was orders of magnitude. Here is an example using the Person.Contact table in AdventureWorks:

Update Statement:

Update [AdventureWorks].[Person].[Contact]
     Set [LastName] = LastName
Where
ContactID = 12

Set Based Trigger:

ALTER TRIGGER [Person].[uContact] ON [Person].[Contact] 
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
; UPDATE [Person].[Contact]
SET [Person].[Contact].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[ContactID] = [Person].[Contact].[ContactID];
END;

Cursor Trigger:

ALTER TRIGGER [Person].[uContact] ON [Person].[Contact] 
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON
;

Declare @ContactId Int
Declare
c_update Cursor For
Select

ContactId
From
inserted

Open c_update Fetch Next From c_update Into
@ContactId

While @@FETCH_STATUS = 0
Begin
UPDATE
[Person].[Contact]
SET [Person].[Contact].[ModifiedDate] = GETDATE()
WHERE
[Person].[Contact].[ContactID] = @ContactId Fetch Next From c_update Into
@ContactId
End Close c_update
Deallocate c_update
END;

I ran the update 6 times against each trigger and here is how they compare using STATISTICE TIME

Statistics Time Results (in ms)
Set-Based Cursor Ratio
Low 5 15 33%
Avg 8.83 18.83 47%
Hi 18 24 75%

So you can see that the set-based trigger is over 50% faster on average in this case where we are only doing a single row update.

Outside Actions in Triggers

Another common issue I see in forums regarding triggers is how to send an email or do some other process that is outside the database. Any action that takes place outside the database engine -- email, file manipulation, linked servers, etc... -- does not belong inside a trigger, in my opinion. Why do these processes not belong in a trigger? I can send mail in SQL Server 2005 using database mail, why can't I use it in a trigger? You can, but you need to make sure you gracefully handle any errors that may happen when sending that email, so your ENTIRE transaction is not rolled back. Remember that the trigger is taking place WITHIN a transaction, so any errors in the trigger will, unless properly handled, rollback the outer transaction as well, so that insert/update/delete will not be successful because of the trigger. Irecommend using a trigger to populate a "staging" table and then use a job or a windows service to do the emailing or other outside process. There may be a delay, but your main transaction will complete without application complexity "hidden" within the trigger.

Hidden Code

Many people do not like triggers because they consider it "hidden" code and I understand their point. I do try to minimize trigger use and when I do use triggers I try to document them well, both within the trigger, and outside in the application documentation. There are times triggers are necessary. In a current application I am working on we have a table that has a "foreign key" column that, based on the type column, can relate to either table A or table B. I can't use a normal foreign key in this case, so I use a trigger to insure referential integrity. I also use triggers for auditing changes on key tables. I could do this in stored procedures or application code, but I want to protect me from me, so I put it in a trigger.

Resources

Trigger Trivia by Andy Warren

Trouble with Triggers blog entry by Conor Cunningham

Triggers...Evil blog entry by Louis Davidson

And as always, SQL BOL.

Total article views: 13042 | Views in the last 30 days: 23
 
Related Articles
FORUM

AFTER UPDATE TRIGGER

Creating an after update trigger

FORUM

Multiple after update triggers on same table

SQL Server 2005 After Update Trigger

FORUM

Trigger defined for Insert, Update

Determine Update or Insert for Trigger defined for Insert, Update

FORUM

After Trigger

Column not available in inserted table in an After trigger (INSERT, UPDATE)

FORUM

How to update field in one table using after insert trigger..?

How to update field in one table using after insert trigger..?

Tags
development    
triggers    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones