Article

Step 4: Be careful how you enforce internal referential integrity through triggers

Serdar Yegulalp, Contributor

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: