〖现象(Symptom)〗
数据库中有如下表:
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/13804621/viewspace-232339/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13804621/viewspace-232339/