Re: Better one big trigger or many small?

I can't help feeling that the other posters may be reading your question in a way that you weren't intending.

I read this as:

    if I have a before insert, before update, and     before delete requirement, should I have one or     three triggers.
NOT as

    if I have three separate before insert requirements     should I have one trigger or three triggers.

If your intention was the latter, then the argument that the order of firing of multiple triggers of the same type is the clinching argument - you MUST, as everyone else has pointed out. have a single before insert trigger.

If your intention was the former, then I would advise the general rule that each class of trigger be kept separate. I haven't tested in detail what the performance impact may be, but if you have just the single trigger to cover before insert, before update and before delete, then it MUST fire, and then you have to have code to determine which event (insert, update, or delete) is occurring.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Carlos wrote in message <1de5ebe7.0202260329.624b085b_at_posting.google.com>...


>I am wondering about the benefits of creating one big trigger (i.e.

>AFTER INSERT, UPDATE OR DELETE) instead of creating many small

>triggers ( AFTER INSERT, AFTER INSERT, AFTER DELETE) per table.

>I think the performance will be better with the three small ones, but

>I'm not sure.

>Another side to concern is the amount of triggers (three or more per

>table), with the complications of maintenance for all of them.

>

>Advices will be appreciated.

>

>Thanks & regards from Spain.