Home » SQL & PL/SQL » SQL & PL/SQL » HOw to apply priority on database triggers
HOw to apply priority on database triggers [message #262782] Tue, 28 August 2007 05:02 Go to next message
shrek
Messages: 1
Registered: August 2007
Junior Member
Dear All,
There are 3 tables, A B and C
2 after insert triggers on table A.
when there is record insert in Table A
then trigger 1 fires to insert data into B and trigger 2 fires to insert data in C.
Now C has a FK to B so before inserting into C , table B should be inserted first.
now from the above case, how we can give the priority to the two triggers so that trigger 1 fires first and then trigger 2.
as we are new to the trigger concept we are not able to find a right solution.
thanks in advance
Rgds,
shrek
Re: HOw to apply priority on database triggers [message #262785 is a reply to message #262782] Tue, 28 August 2007 05:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not possible in pre-11g database.

To guarantee order, create 1 trigger per table per type and add the order of statements yourself
Re: HOw to apply priority on database triggers [message #262788 is a reply to message #262782] Tue, 28 August 2007 05:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can't enforce trigger firing sequence among triggers that fire on the same event. You just can't.*

My advice:
- ONE trigger for each event.
- all executable code, or as much as possible should be in stored procedures. That way you keep control over the flow and you keep your triggers as clean as possible.

MHE

*ok, in 11g you can but it still is wrong
Re: HOw to apply priority on database triggers [message #262861 is a reply to message #262788] Tue, 28 August 2007 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,
Your note in size=1 is the most important, you should use bold.

If 2 triggers are dependent, then why not use one?

Regards
Michel
Re: HOw to apply priority on database triggers [message #262876 is a reply to message #262782] Tue, 28 August 2007 07:19 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,
Before Inserting into Foregin Key use :new insert into parent table .. use before insert trigger there

Thanks and Regards,
Thani.....
Previous Topic: Nested Table,Varray
Next Topic: query for using rownum and order by inthe same query
Goto Forum:
  


Current Time: Fri Dec 09 19:36:13 CST 2016

Total time taken to generate the page: 0.22331 seconds