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

Learn how to properly use triggers to protect SQL Server data integrity rules.

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.)


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

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close