Multiple Triggers Against One Trigger

Question

Scenario:

Every time data is inserted / updated / deleted to / from / from a table, up to 3 things should happen:

• Data should be recorded in a separate table.
• The original integrity should be enforced on implicit linked data (I mean data that should be related to the foreign key relationship, but not: for example, when updating Table1.Name should also update Table2.Name to the same value)
• Arbitrary business logic must be followed

The architecture and design of the database must not be changed, and the requirements must be met by triggers.

Question

Which option is better ?:

• One trigger for each operation (insert / update / delete), which handles several problems (logs, enforces implicit referential integrity and performs arbitrary business logic). This trigger can be called D_TableName ("D" for deletion).

• Several triggers for the operation, which were divided for reasons. They could be called:

  • D_TableName_Logging - to register when something is removed from
  • D_TableName_RI
  • D_TableName_BL

I prefer option 2 because one unit of code has one problem. I am not a database administrator and know enough about SQL Server to make me dangerous.

Are there any good reasons to fix all the problems in one trigger?

Answer 1

Wow, you're in a win-win situation. Anyone who has ever requested that all this be done with the help of triggers needs to be fired and then fired. Do RI through triggers?

You said that the architecture and design of the database should not be changed. However, when you create triggers, you at least change the database schema and, it can be argued, this architecture.

I would probably go with option # 1 and create additional stored procedures and UDFs that deal with BL and RI logging so that the code is not duplicated into separate triggers (triggers could call these saved procs and / or UDFs), I really dislike name the triggers that they proposed in option 2.

By the way, please tell someone in your organization that this is crazy. RI should not be triggered by triggers, and business logic does NOT belong in the database.

Answer 2

Doing all this in one trigger can be more efficient, since you can reduce the number of table operations (without indexing) inserted and deleted .

Also, when you have several triggers, you can set the first and last, which fires, but any others will shoot in random order, so you can not determine the sequence of events if you have more than three triggers for a specific action.

If none of these considerations apply, it is simply a matter of preference.

Of course, it goes without saying that the specification for this with triggers sucks.

Answer 3

I agree with @RandyMinder. However, I would take it one step further. Triggers are the wrong way to approach this situation. The logic you describe is too complex for the trigger mechanism.

You must wrap the insert / update / delete in stored procedures. These stored procedures can control business logic and logging, etc. In addition, they make obvious what is happening. An explicit chain of stored procedures that invoke stored procedures. The chain of triggers that trigger the triggers is defined by insert / update / delete statements that do not invoke an explicit trigger call.

The problem with triggers is that they inject dependencies and locks among disparate tables, and it can be a nightmare to unravel the dependencies. Similarly, it can be a nightmare to spot performance bottlenecks when a problem can be detected in a trigger that calls a trigger that calls a stored procedure that calls a trigger.

Answer 4

If you are using Microsoft SQL Server and you can change the code that executes the DML instructions, you can use the OUTPUT clause to dump updated, inserted, deleted values ​​into temporary tables or memory variables instead of triggers. This will result in minimal performance degradation.