Home » SQL & PL/SQL » SQL & PL/SQL » why can't we use Commit in triggers
why can't we use Commit in triggers [message #35857] Fri, 19 October 2001 08:13 Go to next message
anoop
Messages: 15
Registered: October 2001
Junior Member
What is the reason that we can't use commit in triggers?

----------------------------------------------------------------------
Re: why can't we use Commit in triggers [message #35859 is a reply to message #35857] Fri, 19 October 2001 08:44 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you cant use any transaction control stmts in triggers

let's take following scenario:
1)assume there are 2 triggers (before insert,after insert ) associated with table A .
2)you want to insert a value in to Table B from before insert trigger
and insert a value into table C from after insert trigger when record is being
inserted in table A.

Order of trigger execution in Oracle:
before insert trigger
constraint validation
after insert trigger.

you are going to face following problems when you issue commit in triggers:
a) if you place commit stmt in before insert trigger (if oracle allows),
record will be saved in table B, even though insert stmt of table A fails( due to Constraint
violation).

b) if you place commit stmt in after insert trigger,
records will be saved in tables A,B,C. you cant rollback changes (in table A)
as you already committed transaction in after insert trigger.

HTH
SURESH

----------------------------------------------------------------------
Thankyou Suresh for your time. [message #35860 is a reply to message #35857] Fri, 19 October 2001 10:41 Go to previous message
anoop
Messages: 15
Registered: October 2001
Junior Member
Thankyou Suresh for your time.I got it.Thanks again for your explanation.
Anoop

----------------------------------------------------------------------
Previous Topic: Re: how to find the top 5 biggest value from a query
Next Topic: Inserting text with apostrophes
Goto Forum:
  


Current Time: Sat Apr 04 17:38:11 CDT 2020