触发器oracle题目,oracle触发器问题

以前很少接触oracle中的触发器,早上业务需要就先测试了下，比较简单，代码如下:

--创建测试表

create table lyl_dept as select * from scott.dept;

--创建trigger

create or replace TRIGGER "FOA"."TIB_LYL_DEPT" after update ON lyl_dept FOR EACH ROW

DECLARE

INTERGRITY_ERROR EXCEPTION;

ERRNO    NUMBER;

ERRMSG    CHAR(200);

BEGIN

update lyl_dept  set loc = 'beijing'  where deptno = :OLD.deptno;

EXCEPTION

WHEN INTERGRITY_ERROR THEN

RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);

END;

--测试

update lyl_dept set dname='HOUST' where deptno=20;

commit;

----->>报错如下:

SQL 错误: ORA-04091: 表 FOA.LYL_DEPT 发生了变化, 触发器/函数不能读它

ORA-06512: 在 "FOA.tib_lyl_dept", line 7

ORA-04088: 触发器 'FOA.tib_lyl_dept' 执行过程中出错

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.

---->>说明：

当然这里在业务中更新表数据时也可以同步对该字段做更新，做触发器是after update应该不会出现数据锁定不允许更新的错误吧，请高手指点哈，谢谢