|
|
| Re: why can't we use Commit in triggers [message #35859 is a reply to message #35857] |
Fri, 19 October 2001 08:44   |
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
----------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
