Avoiding Mutating Tables

Asked：ORA-04098 建立Trigger错误

触发器目的：当用户表tblusers 中的修改者reviser发生改变时，将记录所代表的用户的等级employeelevel修改为2。

[@more@]

如下：

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

Create or replace trigger z_tr_sysuser_u
after update of reviser on tblusers
for each row
Declare
cursor cur_change is
select employeelevel, reviser, revisedate, employeeno, employeename
from tblusers
where reviser <> 'MISGROUP'
and employeelevel = 3
for update;
begin
for idx in cur_change loop
update tblusers set employeelevel = 2 where current of cur_change;
execute immediate;
end loop;
end;

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

无法通过编译！

出错原因据说是触发动作，与触发主体都含有DML语句，所以无法通过编译，请问各位如何解决该问题。

Answer 1：

在对tblusers 的for each row的trigger中不能进行对tblusers 进行DML的操作!除非你启用自治事务!即在declare 后增加：

pragma autonomous_transaction;

Answer 2：

需要定义一个包，在each row时，把更新的数据记在包中，然后再写一个触发器，不使用For each row，从包中取出数据执行要触发的操作。

Answer 3：

这个思路是很不错的，我把测试的结果与各位分享一下。

环境：Oracle 9.2

测试表：z_test

目的：当修改z_test表中的col_1时，使用触发器实现对col_3的修改。

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

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

SQL> select * from z_test;

CO COL_2 COL_3 COL_4

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

a 3 Yes fg1
b 3 Yes fg2a 4 Yes fg3c 4 No fg4c 4 No fg5a 3 No fg6c 3 Yes fg7a 3 Yes fg88 rows selected.-------------------------------------------------------------------------------

第一步：建包

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

create or replace package z_change_pkg as
type rowid_tab is table of rowid index by binary_integer;
z_rowid_tab rowid_tab;
emp_idx binary_integer;
end z_change_pkg;

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

因为只是暂存记录，只需要包头就可以了。

第二步：建触发器

（一）、更新记录前设置包数据的触发器z_tr1

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

Create or replace trigger z_tr1
Before update on z_test
Begin
Z_pkg.z_idx := 0;
End z_tr1;

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

（二）、更新记录后将，发生更新的记录的Rowid记录到包中的pl/sql表中

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

create or replace trigger z_tr2
after update on z_test
for each row
begin
z_pkg.z_idx := z_pkg.z_idx + 1;
z_pkg.z_tab(z_pkg.z_idx) := :new.rowid;
end;

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

（三）、实现连带更新。

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

create or replace trigger z_tr3
after update on z_test
begin
for i in 1 .. z_pkg.z_idx loop
update z_test set col_3 = 'OK' where rowid = z_pkg.z_tab(i);
end loop;
end z_tr3;

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

第三步、执行测试

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

SQL> select * from z_test;

CO COL_2 COL_3 COL_4

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

a 3 Yes fg1b 3 Yes fg2a 4 Yes fg3c 4 No fg4c 4 No fg5a 3 No fg6c 3 Yes fg7a 3 Yes fg8
8 rows selected.
SQL> update z_test set col_1='d' where col_4='fg2';
1 row updated.
SQL> select * from z_test;
CO COL_2 COL_3 COL_4-- ---------- ---------- --------a 3 Yes fg1d 3 OK fg2 a 4 Yes fg3c 4 No fg4c 4 No fg5a 3 No fg6c 3 Yes fg7a 3 Yes fg8
8 rows selected.
请看以上粗体记录！

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

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

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

转载于:http://blog.itpub.net/7988176/viewspace-921705/