Trigger Design Recommendations

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   'Order-Header.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 

Make It Simple

It is true that DML triggers are suitable for implementing complex business rules, particularly if those business rules are too complex to be handled by simpler database objects such as constraints. However, just because you are using them to handle complex business rules, you do not have to make your code so complex that it is difficult to understand and follow. It is challenging enough to work with DML triggers; keep them as simple as possible.

Divide and Conquer

In early versions of Microsoft SQL Server, only one After trigger per modification statement could be associated with a table. This physical restriction led developers to produce poor (very complex) code. Features that were not related had to be piled up in a single After trigger. However, this restriction no longer applies. There is no reason to couple the code for multiple After triggers. Each distinct piece of functionality can be implemented in a separate trigger (except in the case of Instead-of triggers).

Do Not Use Select and Print Inside a Trigger

The Print and Select commands are very useful in triggers during the debugging process. However, they can be very dangerous if left in a trigger after it has been introduced into production. These statements generate additional result sets, which might cause the client application to fail if it is not able to handle them or does not expect them.

Do Not Use Triggers at All

If you can implement the required functionality using constraints, do not use triggers! If you can implement the required functionality using stored procedures, and if you can prevent users from accessing your tables directly, do not use triggers!

Triggers are more difficult to implement, debug, and manage. You will save both time and money for your company or your client if you can find simpler ways to implement the required functionality.