今天处理一个程序报错的问题，发现开发人员在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） 〗
去掉事务控制语句