Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario:

Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed.

Question

Given that the solution is to use database triggers, which option is better?:

1) A single trigger per operation (insert/update/delete):

  • I_Table - executed on an insert
  • U_Table - executed on an update
  • D_Table - executed on a delete

Each trigger would handle multiple unrelated items of business logic.

OR

2) Multiple triggers per operation that were segregated by concern:

  • I_Table_Logging - for logging on an insert
  • I_Table_RI - for enforcing referential integrity on an insert
  • I_Table_BL - for executing business logic on an insert
  • U_Table_Logging - for logging on an update
  • U_Table_RI - for enforcing referential integrity on an update
  • U_Table_BL - for executing business logic on an update
  • D_Table_Logging - for logging on an delete
  • D_Table_RI - for enforcing referential integrity on an delete
  • D_Table_BL - for executing business logic on an delete

I prefer option 2 because a single unit of code has a single concern. I am not a DBA, and know enough about SQL Server to make me dangerous.

The architecture and schema of the database must not be changed. There are several places in the schema that should be enforced with explicit relationships, but unfortunately cannot be changed yet. That is the reason I need to manually enforce referential integrity instead of relying on an explicit relationship.

Are there any compelling reasons to handle all of the concerns in a single trigger? I'm specifically concerned about performance, order of execution, and rollback.

share|improve this question

closed as off topic by jcolebrand Jan 3 '13 at 23:42

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
I can see by your history that, you are not a new user at the SE sites. Perhaps you are unaware that we actively discourage cross-posting across SE sites as a policy? Multiple triggers vs a single trigger Please in the future refrain from posting the same question on more than one SE site, and also don't encourage other users to do the same. Thanks. –  ypercube Jan 3 '13 at 22:29
    
As per the above comment, closing this question on Database Administrators in lieu of the question on Stack Overflow –  jcolebrand Jan 3 '13 at 23:42
add comment

2 Answers 2

up vote 3 down vote accepted

My personal preference is to create triggers by functionality, not by DML operation. So I have one handling auditing for insert/update/delete, one doing some kind of business rules or referential integrity (again, for insert/update/delete), another doing some ugly hack to work around undesirable behavior in our client software (possibly only for insert or update), etc.

Keeping each "behavior" in one place reduces the chances of forgetting to update logic that's been duplicated across three separate insert, update, and delete triggers.

The biggest pitfall with triggers is that the INSERTED and DELETED tables are not indexed, and you can't index them directly. Joining them against large data sets performs horribly, and things get really out of hand when you've got many triggers on a table, especially when trigger cascading starts coming into play.

Most triggers I write start out by creating table variables @i and @d with the columns from INSERTED and DELETED that will be needed, giving them both a clustered primary key (since that's the only way you can index a table variable), then filling them with the data from INSERTED and DELETED.

I wrote a short (noncommercial) article about triggers, and it also covers a few other points.

http://dave.brittens.org/2012/03/29/writing-well-behaved-triggers/

share|improve this answer
add comment

I can't stress this enough: triggers are NOT the place for business logic!

I would only use triggers for auditing purposes (e.g. last updated datestamps). Putting business logic inside triggers may seem like a good idea at first, but you will likely end up shooting yourself in the foot over and over again in the future. Triggers tend to get forgotten about and will begin to interfere with other bulk operations you may have to perform at a later date. Time and time again I have seen this happen, and heard a poor hapless DBA scream "GAH! I forgot about all those triggers!!"

Move your business logic into the application, or at least into stored procedures that will be doing the data manipulation. Triggers are not the place for it. Use constraints to enforce data integrity, not triggers.

share|improve this answer
1  
+1 for the following: "Use constraints to enforce data integrity, not triggers." –  AlexKuznetsov Jan 3 '13 at 21:23
    
I agree. This is a legacy system that we are in the process of converting. The context of my question is not "If I were to design a brand new system, what would be the best..." The context is that I'm constrained by the current infrastructure, and under those circumstances, is it better to have a single trigger per operation, or refactor them into multiple triggers with seperate concerns. –  Dan Jan 3 '13 at 22:29
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.