Designing and Modifying Triggers - Trigger Design Recommendations

(Page 2 of 4 )

Since triggers are relatively complex database objects, it is easy to create design, performance, or maintainability problems inside your database. Therefore, I will spend some time pointing out a proper way to use them.

Go Out ASAP

Triggers take time to execute. If your server is very busy and/or other users are locking resources in the database, execution might take much more time than expected. On the other hand, locks that you (or rather SQL Server) have placed in the database while the trigger is executing will not be released until the trigger is finished. Thus, your trigger may increase competition for resources and affect other users and their sessions.

For these reasons, you should always try to exit a trigger as soon as possible. For example, you could start (almost) every DML trigger with the following test:

If @@rowcount = 0
     Return

It will abort further execution of the trigger if no records were changed.

Keep in mind that this If clause must occur at the very beginning of the trigger. If you put it after any other statement, @@rowcount will return the number of records affected by that statement. For example, if you put a simple Print statement at the beginning of the trigger and then this test, the remainder of the trigger will not be executed:

Alter Trigger trOrderStatus_U
On dbo.[OrderHeader]
After Update    -- For Update
As

Print 'Start of trOrderStatus_U'
If @@Rowcount = 0  -- This is always true
                   -- and the rest will NEVER be executed.
     Return

      If Update (OrderStatusId)
      Begin

           Insert into ActivityLog
                  ( Activity,
                    LogDate,
                    UserName,
                    Note)

           Select  
'OrderHeader.OrderStatusId', 
      GetDate(),
      USER_NAME(),
      'Value changed from '
      + Cast( d.OrderStatusId as varchar)
      + ' to '
      + Cast( i.OrderStatusId as varchar)

     From deleted d inner join inserted i
     On d.OrderId = i.OrderId
End

Next: More Trigger Design Recommendations >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Designing and Modifying Triggers

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.