DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How not to use triggers?

Triggers are high maintenance database objects. They should be used as last resort only, for example, enterprise-wide business rule enforcement (applications cannot be changed for a particular business rule) by senior DBA-s and database developers. Triggers should not be used as fix-it-all database objects. For auditing purposes, the OUTPUT clause has been introduced with SQL Server 2005.

Hierarchy of implementing database solutions:

1. Database design

2. Constraints

3. Stored procedures

4. Triggers

Issues with Triggers

1. Triggers don't "complain" when dropped thus may cause database integrity violations and huge application malfunctions

2. Triggers are difficult to view in SQL Server 2008 Object Explorer; even bigger viewing problems in older versions

3. Triggers execute invisible thus causing database development productivity problems

4. Triggers can be disabled, again no "complaint" from trigger as opposed to strored procedure which would cause application to error out, which may break the applications

5. Trigger logic, firing sequence and testing are quite a challenge; for example programming for single vs. multiple rows update/insert/delete/merge statements

6. Triggers can be forgotten thus causing operational problems which is a real big issue in migration

7. Dangerous mix: triggers are the favorite of "newbies" to database programming to fix all the short-comings in design and development; instead of "fix" trigger solutions usually create new problems

8. Triggers run every time on insert/update/delete/merge statements which may cause performance problems especially on large operations

 

Related articles:

http://www.sqlusa.com/bestpractices2008/output/

Exploring SQL Server Triggers by John Papa

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE