Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 25, 2005 - 3:25 am UTC

Last updated: January 20, 2021 - 5:58 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a table and 2 or more after insert/after delete triggers
Can you tell me what will be the order of firing after insert triggers.

I have 2-3 after insert triggers on the same table.
I have tried by finding this using timestamps but all have the same timestamps.

Thanks.

and we said...

all of the BEFORE triggers will fire *in some order* (the documentation goes as far to say "you cannot control the order")

then, all of the BEFORE FOR EACH ROW will fire *in some order*

then, all of the AFTER for each row *in some order*

then all of the AFTER triggers *in some order*


If your triggers depend on the "firing order", you NEED to (must) consolidate them into a single trigger (put them in separate procedures, have one trigger that calls them in order)

UPDATE 15 Jan 2019


11g Added the precedes and follows clauses to triggers. This allows you to define which order trigger fire.

https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B__CJAEJAFB

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Database Developer

A reader, October 25, 2005 - 8:47 am UTC

Good.

Order of Triggers

Padmanabham Tadepalli, October 25, 2005 - 1:41 pm UTC

I have encountered similar situation and found out that the order of triggers firing within the same type of triggers, is decided by the OBJECT_ID of the trigger.

They get fired in Ascending order of OBJECT_ID within the same type.

Tom Kyte
October 26, 2005 - 8:55 am UTC

empirical observation that can easily change anytime oracle feels like it since it is DOCUMENTED that the ordering of firing *cannot be counted on*


Must not rely on ascending Object ID order

Gareth, October 25, 2005 - 3:24 pm UTC

With reference to Padmanabham Tadepalli's comments above I would suggest it is dangerous to rely on this behaviour and that you should follow Tom's advice. The very fact that Oracle goes so far as to say you cannot control the order means that in some future release this behaviour could change. If this happens you have a broken application.

Regards,

Gareth.

Helena Marková, October 26, 2005 - 2:55 am UTC


*in some order*

A reader, October 26, 2005 - 3:36 am UTC

Hi Tom,

Would you like to explain it - *in some order*.
Why it is so in Oracle?
Whats the behaviour of other db's like DB2 and SQL-Server?

Tom Kyte
October 26, 2005 - 11:48 am UTC

don't care about the others too much (sql server - does it even have row triggers or multiple same type triggers? how about db2)

it is "some arbitrary, not to be relied on order"

maybe it'll do trigger1 and then trigger2 today.
maybe it'll do trigger2 and then trigger1 tomorrow.



Find askBill en askGeorge for db2/sql server

Bart, October 26, 2005 - 4:12 am UTC

I don't think Tom is going to find out how other databases behave. Although he knows a lot of them I suspect, he's not working with them.

But you could tell us :)

I can tell you that triggers in sql/server are different beasts (apart from this ordering problem). Not having row triggers for example. Don't know about sqlserver2005.

Maintenance

Martin, October 26, 2005 - 5:44 am UTC

Even if you could control the order that triggers fire, why would you want more than one trigger if you look at it from a maintenance point of view?

*in some order*???

A reader, October 26, 2005 - 5:45 am UTC

I'm here to ask Tom not you "Bart"!

If you don't know enough about SQLServer then how dare you sprinkle your personal thinking upon us? How you get "unleashed" which such a poor knowledge?

For your "little" information, this forum is stuffed with the comparisons among databases over the implementation of features, that's what I'm asking.

Tom Kyte
October 26, 2005 - 12:15 pm UTC

one word....

chill...


it doesn't really matter what the others do in this case, it only matters really what Oracle does - and the firing order is "not defined, not reliable, don't rely on it staying the same"

Manageability

J.Mac., October 26, 2005 - 5:52 am UTC

As Tom said:

"If your triggers depend on the "firing order", you NEED to (must) consolidate them into a single trigger (put them in separate procedures, have one trigger
that calls them in order)"

This has the added bonus of centralizing the PRE-INSERT, or whetever, logic in one place; a glance at one trigger shows you all the procedures about to execute - and in EXACTLY what order.

We have some DBs with numerous triggers firing on the same trigger condition and, believe me, it is confusing sometimes keeping tabs on things.

You can also add conditional logic based on the results of previous procedures (the 'triggers' in the first example) to determine if later procedures (or 'triggers') should fire.

Its akin to having, say, 4 PRE-INSERT triggers: trigger # 3 will fire only if trigger #2 does x, y and z. If #2 does not do x, y and z then move straight on and execute trigger # 4. Doing this in 1 trigger calling 4 PLSQL Procs is easy; in 4 distinct triggers you'll create a whole bunch of work for yourself.

Tom Kyte
October 26, 2005 - 12:16 pm UTC


Resort to Interbase then

Oleksandr Alesinskyy, October 26, 2005 - 7:10 am UTC

Hi,

If zou reallz interested in controlling of trigger order, you may resort to Interbase - it has nice POSITION clause in CREATE TRIGGER statement. The only problem is that 2 triggers with same position fire in unspecified order :).

More seriously, if your application needs a lot of triggers on the same table with predictable order then it is time to redesign your application - it is seriously flawed.

Triigers is a place for relatively simple checks (but not covered by integrity constraints), but not for applicatio logic (you will be soon tired from side-effects and unforeseen behavior). There are very nice samples in Tom's books, especially "Effective Oracle by Design".

Tom Kyte
October 26, 2005 - 12:23 pm UTC

... Triigers is a place for relatively simple checks (but not covered by integrity
constraints), but not for applicatio logic (you will be soon tired from
side-effects and unforeseen behavior). ....

WELL SAID

Poor

Jim, October 26, 2005 - 10:43 am UTC

@Tom, totally understand if you want to remove my post, but:
To "A reader" What an ATROCIOUS way to speak to someone. Quite deplorable. You harangue Bart for his lack of knowledge yet yours patently lacking as well. Have you had a bad day to speak in such a rude manner?
This is not intended to start any kind of slagging match and I won't retort/ answer any rebuttle on this, but I felt it needed said.
Jim

Why more than one then

Lurker, October 26, 2005 - 4:34 pm UTC


if oracle doesn't guarentee the order of the triggers
and documents it as so... then why does it allow people unschooled enough to build more than one trigger of the same type?

No matter how hard you try, you can't build the same constraint twice .

So why multiple triggers of the same type?

Tom Kyte
October 27, 2005 - 6:20 am UTC

why not?

it only matters if you depend on the ordering of firing (a must be before b) and I would think programmers would realize "if a must be before b, then I obviously need a single trigger that calls A and then B - I see nothing in the create trigger syntax that says which comes first after all"


The multiple same type trigger is so that Oracle can create triggers (eg; in support of MV logs for example) AND you can still create triggers as well.

To Lurker....

Matt, October 26, 2005 - 4:51 pm UTC

Being extremely pedantic, you can create the same constraint twice, as long as it's a check constraint:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test (a number);

Table created.

SQL> alter table test add constraint chk_test1 check (a>0);

Table altered.

SQL> alter table test add constraint chk_test2 check (a>0);

Table altered.

But I agree with the point of your post, it would be better if Oracle prevented you from creating more than one trigger of the same type - I'm guessing there may have been some historical reason why it was necessary, but I certainly can't think of a good reason for it now. 
 

Tom Kyte
October 27, 2005 - 6:25 am UTC

see above, we create triggers (internal ones, external ones) in response to certain actions - like a MV log.

If we didn't (we didn't used to in 7.0/7.1/7.2 - I think it was 7.3 or maybe 8.0 that added them) support multiple same type triggers - either you couldn't create a MV log (if you had a trigger of that type) or you couldn't add a trigger of that type after having a MV log.

for example


Sure Sure

Lurker, October 26, 2005 - 4:56 pm UTC

OH Sure... the only constraint type I didn't check before the post!! :)

Provision for multiple UPDATE triggers for different columns

Gary, October 26, 2005 - 7:29 pm UTC

"But I agree with the point of your post, it would be better if Oracle prevented you from creating more than one trigger of the same type"

Its not unreasonable for a requirement for one bit of logic to be executed if columns A or B is updated and another bit if columns B or C is updated. If column B is updated, both 'logics' should fire.
You could consolidate those into a single trigger firing if A,B or C is updated with conditional logic in the trigger. But if you read Tom's blog, this could have performance implications, so as long as there is no dependency between the 'logics' separate triggers might be better:
</code> http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html <code>
"This shows that the :NEW and :OLD column values, when referenced in the trigger, are also used by Oracle to do the restart checking."



A note on table API triggers

andrijas, November 10, 2005 - 10:43 am UTC

Your response was clarifying, but it didn't solve my problem.

I have a table (let's call it, well, a TABLE) which is journaled by means of Des6i generated logic (TABLE_JN and a whole bunch of various API triggers do the job). It is of essence for my application that TABLE is journaled constantly and properly.

When I added a simple single-column-insert-update BRIU trigger TRIGGER on TABLE, I was quite surprised to find out there is no way I can control the sequence of execution of all the BRIU triggers (that is, TRIGGER and the journaling triggers). TRIGGER did fill the column in TABLE as intended, but the corresponding column in TABLE_JN was left empty.

The solution? I simply transfered the logic from TRIGGER to the PRE-INSERT and PRE-UPDATE forms triggers. Not a fancy solution, of course, but I'll live with it as long as it gets the job done. If anyone has any alternative solution for this problem, I’d love to read it.


Order of triggers

Tim, December 06, 2005 - 3:02 pm UTC

Andrijas,
I would disagree with your statement, "... but it didn't solve my problem."

From the rest of your post, it DOES sound like you followed the advice given - which was to consolidate your triggers in question (of the same type - in this case - BEFORE ROW) so as to eliminate the documented uncertainty in the trigger execution order.

You claim this is "Not a fancy solution..." - however I would argue that it is the CORRECT solution.



Trigger order

A Reader, August 15, 2006 - 4:53 pm UTC

Hi Tom

My understanding about the order of triggers is that if there are multiple triggers of the same type on a table, the order is not guaranteed. So, if you have 5 BEFORE INSERT FOR EACH ROW triggers on the same table, they may execute in some order. Is this the only exception when the order is not guaranteed? What about the following scenario?

(1) A row is inserted into a table A.
(2) An after insert row trigger on table A inserts a record into table B.
(3) A before insert row trigger on table B performs some processing.
(4) An after insert statement trigger on table A peforms some processing.

Am I right in assuming that the order will be :

(1) After insert row trigger on table A will fire first.
(2) Before insert row trigger on table B will fire second.
(3) After insert statement trigger on table A will fire third.

Please let me know.

Many Thanks

Tom Kyte
August 15, 2006 - 5:02 pm UTC

yes.

From 11G onward there is a way

Sourav, January 14, 2019 - 3:01 pm UTC

In Oracle 11G you can use the PRECEDES or FOLLOWS clauses to control the execution. Please feel free to correct me if I am wrong.
Chris Saxon
January 15, 2019 - 3:13 pm UTC

You are correct. The precedes/follows clause allows you to enforce an order on your rows.

DW, January 19, 2021 - 6:54 pm UTC

Should say 21G?
Connor McDonald
January 20, 2021 - 5:58 am UTC

No, precedes/follow has been around for long time

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.