Rexdf

The devil is in the Details.

关于删除时的触发器的问题

| Comments

..

http://v.youku.com/v_show/id_XMTkyNTk0NDQ0.html

 

在做一个触发器要求从在一个表中删除完某种部门的人的时候在另一个部门表中把这个部门也删掉。 :-P  触发器这么写的：

create or replace trigger scott.emp_delete before delete on emp for each row
declare
v_temp number(2);
begin
select deptno into v_temp from emp where deptno=:old.deptno;
if sql%NOTFOUND THEN
delete from dept where dept.deptno=v_temp;
end if;
end;

发现怎么都会报下面的错误

SQL> delete from emp2 where deptno=15;
delete from emp2 where deptno=15
*
第 1 行出现错误:
ORA-04092: COMMIT 不能在触发器中
ORA-06512: 在 "SCOTT.EMP2_DELETE", line 4
ORA-04088: 触发器 'SCOTT.EMP2_DELETE' 执行过程中出错

按网上的说法是Avoiding Mutating Tables，要用到自制事务pragma autonomous_transaction;

CREATE OR REPLACE TRIGGER TR_DEL_CABLE
         AFTER DELETE ON wire_terminal
FOR EACH ROW
DECLARE 
v_count number;
pragma autonomous_transaction;
BEGIN
        select count(*) into v_count from wire_terminal where cable_id = :Old.cable_id and cable_side = :Old.cable_side;
  if v_count = 0 then
            if :Old.cable_side = 1 then
                    update cable set side_1_desc= '' where cable_id = :Old.cable_id;
        commit;
            else
                    update cable set side_2_desc= '' where cable_id = :Old.cable_id;
              commit;
      end if;
        end if;

END TR_DEL_CABLE;

自治事务很可能是误用。你在触发器里看到的其实是修改之前的数据，因为主事务还未提交。
解决这个问题首选的办法：不要用触发器；

符一个收集的状况：
1，变化表是被DML语句正在修改的表，delete cascade也是变化表。
限制表：可能需要对参考完整性限制执行读操作的表。

比如emp表上创建了触发器，如果对表emp执行DML，则由于emp的deptno是参照
dept的，所以这时emp是变化表而dept是限制表。

2，mutating table：

create or replace trigger emp_trigger after update of sal on emp for each row
declare
rsal emp.sal%type;
begin
select sal into rsal from emp where ename='KIN';
end;
/

这个触发器虽然可以创建成功，但是如果真的在emp列sal上执行了dml则这时emp立即变成mutating表，
所以触发时会发生错误。

ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它

但是这个限制只对行级触发器起作用，对语句级的不起限制：

create or replace trigger emp_trigger after update of sal on emp
declare
rsal emp.sal%type;
begin
select sal into rsal from emp where ename='KING';
end;

而且如果对表进行插入一行操作，那么oracle不把触发表当成mutating table，插入多行则当成mutating

table：

create or replace trigger emp_trigger before insert on emp for each row
declare
rsal emp.sal%type;
begin
select sal into rsal from emp where ename='KING';
end;

insert into emp(ename,empno,sal,deptno) values('Tank',7748,4550,10);

这条语句可以执行，且触发器不出错。

下面就不行了：

select * from test;

X                   Y          Z          M
---------- ---------- ---------- ----------
Tii              3342       6700         10
Poo              3355       6720         30

insert into emp(ename,empno,sal,deptno) select * from test;

ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它

就算子查询返回一条也不行：

select * from test;

X                   Y          Z          M
---------- ---------- ---------- ----------
Poo              3355       6720         30

insert into emp(ename,empno,sal,deptno) select * from test;

ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它

Comments

twinkle: test

rexdf: 测试 :mrgreen: :( :( :-P 8-O

winkle: 嘿嘿 [img]http://blog.rexdf.org/wp-content/uploads/2011/11/1.png[/img]

rexdf: test 要注册的才能收到评论回复是吧

Tracy: 留点言吧。。。 :wink: :wink: 小伙子不错啊！！！~~~

rexdf: 呀 小伙也厉害啊 :(asd :(asd

Comments