| Commit in a Trigger [message #1256] |
Mon, 15 April 2002 07:05  |
KK
Messages: 24
Registered: March 2002
|
Junior Member |
|
|
Hi
I have a trigger on a table - T1, that will fire before delete. I then need to insert into another table - T2 when this trigger fires and run a report based on T2. Therefore I will need to commit these records inserted into the table - T2.
I get an error when I put the commit in the trigger. Any suggestions on how I can resolve this issue so I can commit the records would be helpful!
I am using Oracle 8.0.5.
Thank you!
|
|
|
|
|
|
| Re: Commit in a Trigger [message #1269 is a reply to message #1256] |
Tue, 16 April 2002 02:13  |
Sergio
Messages: 11
Registered: March 2002
|
Junior Member |
|
|
|
You can't use commit in procedure, function or trigger directly. You have to use package dbms_sql instead or do commit command in the session which fired the trigger.
|
|
|
|
| Re: Commit in a Trigger [message #1271 is a reply to message #1258] |
Tue, 16 April 2002 04:39 |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
Hi
The commit can be used only when the trigger is fired and the control comes back to the original proc/function. If u r calling a function/proc from a trigger then u can not have a commit there as that wud be the current transaction session inside the main transaction.
--solutions proposed
1)Apply commit just after the DML's (insert/update/delete) in the function.
2)In oracle 8i onwards, u can make a commit statement from the trigger by calling an AUTONOMOUS Transaction proc/functions and there u can have commit.
eg the syntax wud be like this
create or replace PROC/FUN asd
PRAGMA AUTONOMOUS_TRANSACTION
IS/AS
-----------
and call this function from the trigger.
Any clarifications r welcomed...
Amit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
