Application of Oracle 11g compound triggers

Question

I just have a very quick question about the use of compound triggers.

I'm very new to database/sql/oracle and have a uni task where i have had to create triggers for the following:

1. Insert into log table when sales person creates an order
2. Update on order table whe order is ready to ship (plus insert into log)
3. Prevent INSERT, UPDATE, DELETE after 5pm friday until 9am monday.

I have succesfully implemeted the triggers but have just become aware of the compound triggers in 11g. Would it be appropriate to look into combining the above into one complound trigger? Is that what they are for or am I missing the point?

Many thanks for looking at this rather vague question.

Answer 1

IMO it wouldn't be appropriate. A compound trigger allows you to trap the four different trigger points (before statement, before action, after action, and after statement) for a given action on a given table, but it looks like A) you're triggering on multiple tables, and B) you don't have a need to service all the different trigger points. (BTW, "action" as I've used it here can mean different statement types, e,g. BEFORE INSERT, UPDATE, DELETE...).

Compound triggers make for a nice, clean, and compact way to work around the never-popular ORA-04091 MUTATING TABLE error, but for the most part I don't find them necessary or useful as a general-purpose do-all replacement for "normal" triggers. For an example, see my answer to this question.

Share and enjoy.

Answer 2

wow - compound triggers - didn't know they even existed...

I quickly looked up the documentation

The main reason to use compound triggers is explained as follows:

The compound trigger makes it easier to program an approach where you want the actions you implement for the various timing points to share common data. To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire.

So unless you needed package variables to implement your functionality there wouldn't be a reason for using them. And your code wouldn't be 10g compatible anymore.