开发PL/SQL子程序——触发器——使用触发器注意事项

---

---

当编写dml触发器时，触发器代码不能从触发器所对应的表中读取数据。例如，如果要基于emp表建立触发器，那么该出发起的执行代码不能包含对emp表的查询操作。尽管机那里触发器时不会出现任何错误，但在执行相应触发操作时会显示错误的信息。假定要确保雇员的新工资不能超过当前的最高工资，并使用触发器实现；

create or replace trigger tr_emp_sal
before update of sal on emp for each row
declare maxsal number(6,2);
begin
select max(sal)into maxsal from emp ;
if :new.sal>maxsal then
raise_application_error(-20001,'超出工资上限');
end if;
end;
/

---

update emp set sal =60000 where empno=7788;

在行 1 上开始执行命令时出错:
update emp set sal =60000 where empno=7788
错误报告:
SQL 错误: ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.TR_EMP_SAL", line 3
ORA-04088: 触发器 'SCOTT.TR_EMP_SAL' 执行过程中出错
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.

---