希望在插入数据后,将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.触发器
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.触发器
解决方案 »
- 在oracle里面调用java,参数是不是有长度限制?
- rman登录提示权限不足
- 关于一个用户操作日志记录分类插入Oracle数据库的问题
- [收集贴]本贴收集SQL常用的存储过程和自定义函数,大家有的话发出来晒晒!
- 表索引问题
- 问一个sql语句问题
- 向orcal插入数据,提示ORA-00911,怎么回事啊????????????急!!!!!!!!!!!!!!!!!!!!
- 如何在oracle 下执行下面的语句
- 请教个 关于Oracle存储过程的问题
- oracle设置问题,非高手莫入????在线等待,急 急 急 急
- 如何将oracle数据库中的数据按星期算出每个星期的平均值? 每条数据都有时间字段。
- oci编程批量插入
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 不能去啊。
将触发器加上自治事务,可正常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;