Triggers make it possible to enforce very powerful data integrity rules, and they are designed to protect data integrity. When a trigger fires, the trigger action is automatically encapsulated within the context of a transaction; you don't need to place a BEGIN TRANSACTION at the start and a COMMIT TRANSACTION at the end of the trigger code. You can do this without ill effects; it just creates a nested transaction. (In some cases it can be useful -- for instance, if you want the additional transaction to be reported by @@TRANCOUNT.)
That said, triggers can do as much harm as they do good. Don't use them when other, simpler mechanisms can get the same job done. In other words, don't use an INSERT trigger to screen data when a simple column constraint will do. Also, triggers can be very difficult to debug if they're not documented thoroughly. It's not obvious that a table has triggers on it unless you go looking for them. The quick-and-dirty way to find out what triggers are present in a database is to run this script in Query Analyzer:
use <database_name> select name as 'Trigger', object_name(parent_obj) as 'Table' from sysobjects where xtype = 'TR'
(<database_name> is, of course, the name of the database in question.)
Requires Free Membership to View
ENSURING DATA INTEGRITY IN SQL SERVER
Home: Introduction
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well! Copyright 2005 TechTarget |
Join the conversationComment
Share
Comments
Results
Contribute to the conversation