Designing and Modifying Triggers - More Trigger Design Recommendations
(Page 3 of 4 )
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.
Next: Transaction Management in Triggers >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 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.
|
|
/ 5 
