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".

EDIT Thank you very much for your replies! I will go with multiple triggers to preserve my sanity. I just wanted to check if this was one of those "Do not ever do that"-things.

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".

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".

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".

EDIT Thank you very much for your replies! I will go with multiple triggers to preserve my sanity. I just wanted to check if this was one of those "Do not ever do that"-things.