How to use COMMIT in Procedure called by trigger
21,697
You can not have a COMMIT inside a trigger. Your UPDATE will be committed as soon as the INSERT to the table1 is committed.
But to achieve what you want you can use an autonomous transaction. For example,
CREATE OR REPLACE TRIGGER mytrg
AFTER INSERT ON table1 FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
myschema.myproc(:new.ID, :new.NAME, :new.TYPE_CODE, :new.LANGUAGE);
COMMIT;
END;
Author by
C.K
Updated on July 05, 2022Comments
-
C.K almost 2 years
I have the below trigger(mytrg) which calls a procedure(myproc) that will update table2 if any insert in table1. I have "COMMIT" statement in the procedure after the data is updated in table2. But when there is an insert in table1, i get the below error.
Error report: SQL Error: ORA-04092: cannot COMMIT in a trigger ORA-06512: at "myschema.myproc", line 63 ORA-06512: at "myschema.mytrg", line 2 ORA-04088: error during execution of trigger 'myschema.mytrg' 04092. 00000 - "cannot %s in a trigger" *Cause: A trigger attempted to commit or rollback. *Action: Rewrite the trigger so it does not commit or rollback. **Trigger:** create or replace trigger mytrg after insert on table1 for each row begin myschema.myproc(:new.ID, :new.NAME, :new.TYPE_CODE, :new.LANGUAGE); end;
Need to know how to commit the update.
Thanks