Multiple triggers vs a single trigger

Question

Scenario:

Each time data is inserted/updated/deleted into/in/from a table, up to 3 things need to happen:

1. The data needs to be logged to a separate table
2. Referencial integrity must be enforced on implicit related data (I'm referring to data that should be linked with a foreign key relationship, but isn't: eg. When a updating Table1.Name should also update Table2.Name to the same value)
3. Arbitrary business logic needs to execute

The architecture and schema of the database must not be changed and the requirements must be accomplished by using triggers.

Question

Which option is better?:

1. A single trigger per operation (insert/update/delete) that handles multiple concerns (logs, enforces implicit referencial integrity, and executes arbitrary business logic). This trigger could be named D_TableName ("D" for delete).

2. Multiple triggers per operation that were segregated by concern. They could be named:

   • D_TableName_Logging - for logging when something is deleted from
   • D_TableName_RI
   • D_TableName_BL

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.

Are there any compelling reasons to handle all of the concerns in a single trigger?

Solution 1

Wow, you are in a no-win situation. Who ever requested that all this stuff be done via triggers should be shot and then fired. Enforcing RI via triggers?

You said the architecture and schema of the database must not be changed. However, by creating triggers, you are, at the very least, changing the schema of the database, and, it could be argued, the architecture.

I would probably go with option #1 and create additional stored procs and UDFs that take care of logging, BL and RI so that code is not duplicated amoung the individual triggers (the triggers would call these stored procs and/or UDFs). I really don't like naming the triggers they way you proposed in option 2.

BTW, please tell someone at your organization that this is insane. RI should not be enforced via triggers and business logic DOES NOT belong in the database.

Solution 2

Doing it all in one trigger might be more efficient in that you can possibly end up with fewer operations against the (un indexed) inserted and deleted tables.

Also when you have multiple triggers it is possible to set the first and last one that fires but any others will fire in arbitrary order so you can't control the sequence of events deterministically if you have more than 3 triggers for a particular action.

If neither of those considerations apply then it's just a matter of preference.

Of course it goes without saying that the specification to do this with triggers sucks.

Solution 3

I agree with @RandyMinder. However, I would go one step further. Triggers are not the right way to approach this situation. The logic that you describe is too complicated for the trigger mechanism.

You should wrap inserts/updates/deletes in stored procedures. These stored procedures can manage the business logic and logging and so on. Also, they make it obvious what is happening. A chain of stored procedures calling stored procedures is explicit. A chain of triggers calling triggers is determined by insert/update/delete statements that do not make the call to the trigger explicit.

The problem with triggers is that they introduce dependencies and locking among disparate tables, and it can be a nightmare to disentangle the dependencies. Similarly, it can be a nightmare to determine performance bottlenecks when the problem may be located in a trigger calling a trigger calling a stored procedure calling a trigger.

Comments

[Martin Smith]

The problem with stored procedures and UDFs here is that they have no access to inserted and deleted except if you are going to copy them into a TVP.

[Randy Minder]

@MartinSmith - Or put them in a temp table first. Hard to say what the right approach is given what little requirements were presented.

[Martin Smith]

@RandyMinder - functions can't access #temp tables.

[Randy Minder]

@MartinSmith - Yes, that's true. UDFs might not work in this case, and probably are not necessary if stored procs will do the job.

[ypercubeᵀᴹ]

Business logic belongs where business wants to.

[Randy Minder]

@ypercube - Wrong. The business doesn't understand what the strenghts and weaknesses of a database are.

[ypercubeᵀᴹ]

I really don't understand your last paragraph. RI models business logic, too. Why does that belong in the database?

[Randy Minder]

@ypercube - Do you know what a foreign key is?

[Dan]

@RandyMinder what is your preferred naming convention?

[Randy Minder]

@Dan - Depends on what option you go with. If it's option 1, then our standard is TableName_<Operation(s)>. So, it might be something like MyTable_Insert, MyTable_Update or MyTable_InsertUpdate. This way it's clear when the tigger fires, by looking at the name. BTW, we're phasing out the use of triggers at our company. Except for isolated uses, they cause far more problems than they solve. Our databases are strictly for the persisting of data. All BL, logging etc. is contained in middle-tier C# service code.

[Dan]

I totally agree with you that triggers are not how this should be handled. The reason that is a requirement has to do with supporting a legacy solution.