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 insertU_Table
- executed on an updateD_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 insertI_Table_RI
- for enforcing referential integrity on an insertI_Table_BL
- for executing business logic on an insertU_Table_Logging
- for logging on an updateU_Table_RI
- for enforcing referential integrity on an updateU_Table_BL
- for executing business logic on an updateD_Table_Logging
- for logging on an deleteD_Table_RI
- for enforcing referential integrity on an deleteD_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.