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

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.