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.
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?
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.
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.
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".
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?
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.
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, Id 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
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.
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?
January 20, 2021 - 5:58 am UTC
No, precedes/follow has been around for long time