OraFAQ Forum: SQL & PL/SQL » Order of Trigger Types

Home » SQL & PL/SQL » SQL & PL/SQL » Order of Trigger Types (11g Release 11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order of Trigger Types [message #666462]

Mon, 06 November 2017 10:30

whdyck
Messages: 25
Registered: May 2017

Junior Member

In Oracle's documentation, I see this:

Quote:

The database executes all triggers of the same type before executing triggers of a different type. If you have multiple triggers of the same type on the same table, and the order in which they execute is important, use the FOLLOWS clause. Without the FOLLOWS clause, the database chooses an arbitrary, unpredictable order.

Fair enough. But is there a specified order of trigger types? I.e., Do all simple triggers fire before compound triggers? Or do the trigger types fire in a random order also?

Thanks.

Wayne

Report message to a moderator

Re: Order of Trigger Types [message #666464 is a reply to message #666462]

Mon, 06 November 2017 10:39

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The order seems to be:

Run all BEFORE statement triggers that apply to the statement.
Run all BEFORE row triggers that apply to the statement.
Run all AFTER row triggers that apply to the statement.
Run all AFTER statement triggers that apply to the statement.

According to:

https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm#i13362

[Updated on: Mon, 06 November 2017 10:41]

Report message to a moderator

Re: Order of Trigger Types [message #666465 is a reply to message #666464]

Mon, 06 November 2017 11:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575882200346616184
http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html
https://stackoverflow.com/questions/460316/are-database-triggers-evil
https://oracle-base.com/blog/2016/08/24/database-triggers-use-at-your-own-risk/

Please Read & HEED!

Report message to a moderator

Re: Order of Trigger Types [message #666468 is a reply to message #666465]

Mon, 06 November 2017 18:40

whdyck
Messages: 25
Registered: May 2017

Junior Member

BlackSwan wrote on Mon, 06 November 2017 11:01

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575882200346616184
http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html
https://stackoverflow.com/questions/460316/are-database-triggers-evil
https://oracle-base.com/blog/2016/08/24/database-triggers-use-at-your-own-risk/

Please Read & HEED!

@BlackSwan:
Yes, I've seen your rant before about the evil of triggers. Unfortunately, I don't live in the same purist paradise that you do: I have a real job in a development shop where I don't have the luxury of telling my boss I won't code triggers: they were here long before I came along, and no one's gonna listen to me if I tell them to get rid of the triggers.

Since triggers are listed as a topic for this forum, please consider just passing on questions about them if you're unable or unwilling to answer. Thanks.

Report message to a moderator

Re: Order of Trigger Types [message #666474 is a reply to message #666465]

Tue, 07 November 2017 08:18

Bill B
Messages: 1971
Registered: December 2004

Senior Member

BlackSwan wrote on Mon, 06 November 2017 12:01

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575882200346616184
http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html
https://stackoverflow.com/questions/460316/are-database-triggers-evil
https://oracle-base.com/blog/2016/08/24/database-triggers-use-at-your-own-risk/

Please Read & HEED!

You are being unrealistic. If all coding going against the database is your own development then you can easily avoid triggers. However if you are running a canned application where you have to change certain actions by the package then the ONLY way to do it on the fly is to use triggers. I live in the real world where you have to make compromises to make things work.

Report message to a moderator

Previous Topic:	how can get five days
Next Topic:	Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? split from http://www.orafaq.com/forum/t/161791/

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:21:40 CDT 2024