触发器update问题

[zhongzuo1981]

希望在插入数据后，将status字段的值由1改为0，能不能通过触发器实现？

多谢！！！

触发器代码如下：
create or replace
TRIGGER TCM_TRIGGER_BLOGCOMMENTCHECK
AFTER INSERT ON BLOGCOMMENTCHECK
FOR EACH ROW

BEGIN
update NEWSBLOG.blogcommentcheck set status=0 where status=1;
END;

在执行insert时报错
错误报告:
SQL 错误: ORA-04091: table NEWSBLOG.BLOGCOMMENTCHECK is mutating, trigger/function may not see it
ORA-06512: at "NEWSBLOG.TCM_TRIGGER_BLOGCOMMENTCHECK", line 2
ORA-04088: error during execution of trigger 'NEWSBLOG.TCM_TRIGGER_BLOGCOMMENTCHECK'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

[ilovemk]

create or replace TRIGGER TCM_TRIGGER_BLOGCOMMENTCHECK AFTER INSERT ON BLOGCOMMENTCHECK BEGIN update NEWSBLOG.blogcommentcheck set status=0 where status=1; END; 去掉for each row

[zhongzuo1981]

引用 1 楼 ilovemk 的回复:

create or replace TRIGGER TCM_TRIGGER_BLOGCOMMENTCHECK AFTER INSERT ON BLOGCOMMENTCHECK BEGIN update NEWSBLOG.blogcommentcheck set status=0 where status=1; END; 去掉for each row ……

update 语句要改一下 where id=:new.id 所以for each row 不能去啊。

[ilovemk]

google自治事务

[zhongzuo1981]

引用 3 楼 ilovemk 的回复:

google自治事务

将触发器加上自治事务，可正常insert，但触发器里的update没起作用。status的值还是insert的值。 create or replace TRIGGER TRIGGER_COMMENT AFTER INSERT ON BLOGCOMMENTCHECK FOR EACH ROW declare PRAGMA AUTONOMOUS_TRANSACTION; BEGIN update BLOGCOMMENTCHECK set status=0 where id=:new.id; commit; END;

[ilovemk]

首先定义一个package，package里面定义一个表对象的全局变量或者数组，假设对象名为v_array 再建三个触发器，第一个是BEFORE INSERT，这个触发器初始化v_array，第二个是AFTER INSERT FOR EACH ROW，这个触发器不做实际的更新操作，只是将:new.id插入v_array，第三个触发器是AFTER INSERT，这个触发器遍历v_array并更新BLOGCOMMENTCHECK表，做完收工。