multi_table2.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 72 行

/*
变异表：当不读取被改变的行时，是否还是变异表呢？

*/

create or replace trigger check_salary
before insert or update  on emp
for each row
declare 
 v_minsalary emp.sal%type;
 v_maxsalary emp.sal%type;
 v_sal emp.sal%type;
begin
 --只从emp取一个值，可不可以呢？
 v_minsalary :=100;
 v_maxsalary :=2000;
 select sal into v_sal from emp where ename='KINGa';
 if   :new.sal > v_sal then
   raise_application_error(-20254,'more than king');
 end if;
end;


insert into emp (empno,ename,sal) values(111,'008',6000);
ERROR at line 1:
ORA-20254: more than king
ORA-06512: at "SCOTT.CHECK_SALARY", line 11
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SALARY'

insert into emp (empno,ename,sal) values(111,'008',10);
--没有被认为是变异表
--ok
update emp set sal=100 where empno=101;

ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECK_SALARY", line 9
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SALARY'
--被认为是变异表！
--trigger 是before insert or update 为什么不同呢？
--oracle已声明：For a single row INSERT, 变异表
--for AFTER row triggers,but not for BEFORE row triggers. INSERT



-----------------------------------------before改为after-----------

create or replace trigger check_salary
after insert or update  on emp
for each row
declare 
 v_minsalary emp.sal%type;
 v_maxsalary emp.sal%type;
 v_sal emp.sal%type;
begin
 --只从emp取一个值，可不可以呢？
 v_minsalary :=100;
 v_maxsalary :=2000;
 select sal into v_sal from emp where ename='KINGa';
 if   :new.sal > v_sal then
   raise_application_error(-20254,'more than king');
 end if;
end;


insert into emp (empno,ename,sal) values(113,'008',6);
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECK_SALARY", line 9
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SALARY'