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） 〗

   去掉事務控制語句