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?

asked 16 Dec '11, 10:30

Martin's gravatar image

accept rate: 14%

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

(16 Dec '11, 10:48) Volker Barth

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.
permanent link

answered 16 Dec '11, 11:27

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%


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

(19 Dec '11, 03:31) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Dec '11, 10:30

question was seen: 1,410 times

last updated: 19 Dec '11, 03:31