Efficiciency of multiple small triggers versus one large trigger on SQL Server

Question

I have been given the honourable task of adding - very carefully! - a few triggers to our ERP system's database, to get data - "in real time" - from the ERP system to other pieces of software running at our company. The triggers will not make any changes to the data, just pick a few fields from the inserted/updates rows, roll them up in a XML message and send them to a message queue. There will be no JOINs, either. (Whether or not this is a good idea in to begin with is a question for another time.)

I can imagine writing several triggers, one for each scenario. Or I could could write two large triggers, one for INSERT, and for UPDATE. With enough force, I could probably even boil that down to just one huge trigger that runs both on INSERT and on UPDATE.

From a testing/debugging/maintenance point of view, several self-contained triggers have obvious advantages: Each one can be written, tested and enabled/disabled individually. Each trigger would check if whatever just happened to the respective table is relevant to that trigger, if so, it would do its thing, and if not, it would just quit.

However, I am worried that multiple triggers might cause performance issues. The tables I am supposed to attach these trigger(s) see frequent INSERT and UPDATE queries as part of normal operation[1]. (OTOH, the load on the database server is relatively mild so far.)

Are there any general guidelines on which approach is better?

Thank you very much for any information you might be able to share, Benjamin

[1] Frequent in this case means "a couple of dozen transactions per minute on a busy day".

Answer 1

_{Community wiki answer:}

You already sound like you know the right answer. The best approach is the one that has the trigger code run the fastest with the least amount of code. If it were me, I'd have one trigger per action. Will there be duplicate code - absolutely - but it saves you for when anything needs to be changed and can be customized per situation.

You could try both and then simulate load to see which you like/performs the best. Aaron Bertrand found there was observable additional overhead with multiple triggers performing the same operations, but YMMV.

However, a couple dozen transactions per minute doesn't sound like a tremendous amount of activity. Maintainability 'might' be able to justify a small amount of overhead. There are numerous advantages to using separate 'single-function' triggers. As you indicated, they have a single purpose and can be individually tested (by different people), enabled, disabled. Isolating the processing into separate 'single-function' triggers 'might' reduce the risk involved in future maintenance.

Of course, final testing of the individual triggers cannot be by different people, as you need to ensure the triggers work properly in unison. And, while future maintenance of smaller chunks of code is logically easier, you cannot ignore the other triggers when making future changes, as something that causes no issues in trigger A could completely mess up what trigger C is doing.