ORA-04092: cannot COMMIT in a trigger错误的解决

      今天处理一个程序报错的问题，发现开发人员在trigger使用了rollback, commit 等控制语句，报错信息： ORA-04092  . 

      ORA-04092 cannot string in a trigger

Cause: A trigger attempted to commit or roll back.

Action: Rewrite the trigger so it does not commit or roll back

 

------------------------------------------------------------
CREATE OR REPLACE TRIGGER ASN.DBS_INBOUND_TAIL
AFTER UPDATE
ON ASN.DBS_INBOUND_TAIL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar  NUMBER;
tmpVar2 NUMBER;

BEGIN
  tmpVar  := 0;
  tmpVar2 := 0;

   SELECT COUNT(*) INTO tmpVar FROM DBS_INBOUND_DTL
   WHERE  SHIPPING_NO=:NEW.SHIPPING_NO;

   SELECT SHIPPING_QTY INTO tmpVar2 FROM DBS_INBOUND_HDR
   WHERE  SHIPPING_NO=:NEW.SHIPPING_NO;

   IF tmpVar <> tmpVar2 THEN
        ROLLBACK;
    END IF;

END ;
/

---------------------------------------------------------------

 

 

 

网上实验转帖： 

---------------------------------------------------------------

SQL> create table t1

 2 (a int);

Table created.

 

SQL> desc students4

 Name                                     Null?   Type

 ----------------------------------------- -------- ---------------

 NAME                                              VARCHAR2(20)

 DEPT_ID                                           NUMBER

 

 

现象一：在触发器中使用DDL语句。

Step01：创建触发器tri_pt，触发器的主体将删除表t1。

SQL> create or replace trigger tri_pt

 2 after delete on students4

 3 for each row

 4 DECLARE

 5   v_dept_id int;

 6 begin

 7  execute immediate 'drop table t1';--DDL操作

 8 end;

 9 /

Trigger created.

 

〖小贴士（Tip）   〗

DDL语句隐含commit。

 

Step02：删除表students4的数据，这个表将点燃触发器tri_pt。

SQL> delete from students4 where name='MARY';

delete from students4 where name='MARY'

            *

ERROR at line 1:

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at "TEST.TRI_PT", line 4

ORA-04088: error during execution of trigger 'TEST.TRI_PT'

 

 

现象二：

Step01：创建触发器tri_pt，触发器的主体中使用了commit语句。

SQL> create or replace trigger tri_pt

 2 after delete on students4

 3 DECLARE

 4   v_dept_id int;

 5 begin

 6  commit;

 7 end;

 8 /

 

Trigger created.

 

Step02：删除表students4的数据，将点燃触发器tri_pt。

SQL> delete from students4 where name='MARY';

delete from students4 where name='MARY'

            *

ERROR at line 1:

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at "TEST.TRI_PT", line 4

ORA-04088: error during execution of trigger 'TEST.TRI_PT'

 

 

〖原理（Cause） 〗

DML（delete/update/insert）触发器中不能使用DDL（CREATE，DROP，ALTER）语句，也不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有事物控制语句。

 

〖小贴士（Tip）   〗

系统级触发器（System Triggers）中可以使用DDL语句。

 

〖方法（Action） 〗

   去掉事务控制语句

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-433098/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-433098/