What is the Best Practise for multiple triggers

[MCMartin]

If one has multiple actions for the same event (e.g. after insert), what is the best practise

1. multiple individual triggers or
2. one trigger covering all actions

Any advantage of the first or second approach over the other?

[VolkerBarth]

I have no "best practise" answer - it may also depend on whether you are used to write a single trigger for multiple actions (e.g. a combined insert/update trigger)...

[Former Member · Accepted solution]

In the main I don't think it matters all that much, but here are some issues to consider:

1. If compatibility with other DBMS is at all a concern, be aware that most other systems either (a) do not support triggers with multiple actions, or (b) do not support the ORDER clause so that you can precisely specify the trigger firing order; these considerations also apply to Sybase ASE. In the SQL standard, trigger firing order is based on trigger CREATION time and several systems follow that convention.
2. Also, concerning compatibility, most other DBMS do not support the trigger action variables INSERTING, UPDATING, and DELETING so that you can determine within the trigger what base operation invoked it.
3. Much depends on how complex the logic is within the trigger, depending on what logic has to be executed for each individual change. If the trigger logic is so complex that you split the logic into multiple sub-procedures, then having more than one trigger may make the code more readable (but subject to compatibility concerns as mentioned above). This is particularly a concern when trying to handle errors.

[VolkerBarth · Accepted solution]

I would add that

if compatibility with other DBMS is at all a concern

you usually will not be able to use the rich set of trigger features in SQL Anywhere - possibly no BEFORE triggers, no ROW LEVEL triggers (with their much easier semantics IMHO) - at least I always feel "in limited mode" when I have to deal with MS SQL triggers...