如何在触发器调用的过程中使用COMMIT [英] How to use COMMIT in Procedure called by trigger

问题描述

我有下面的触发器(mytrg)，它调用一个过程(myproc)，如果在table1中有任何插入，它将更新table2.在表2中更新数据后，我在过程中有"COMMIT"语句.但是当table1中有一个插入时，我得到以下错误.

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.

谢谢

推荐答案

触发器中不能包含COMMIT.提交对table1的INSERT后，将立即提交您的UPDATE.

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;

这篇关于如何在触发器调用的过程中使用COMMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！