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