關於觸發器(Trigger)對於變異表(Mutating table)的操作

很多時候需要在觸發器中對觸發表進行查詢，但在行級觸發器中是不允許的。

 

下面用HR用戶的employees表進行簡單的說明。

虛擬需求：對新加入的員工的工資和更新員工的工資時，不能設置為比該員工的上司的工資高。

 

首先，建立一個after的行級觸發器

create or replace trigger trg_update_salary after update of salary or insert on employees for each row declare cursor cur_manager_salary(mgr number) is select salary from employees where employee_id = mgr; -- 查詢上司工資的游標 begin if :new.manager_id is not null then for manager in cur_manager_salary(:new.manager_id) loop if :new.salary > manager.salary then -- 比上司工資高就產生一個錯誤 raise_application_error(-20001, 'Salary cannot greater than the manager''s'); end if; end loop; end if; end;

 

然後，運行insert語句和update語句都報

ORA-04091: 表 HR.EMPLOYEES 发生了变化, 触发器/函数不能读它

如下：

insert into employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id) values (9999, 'wugy', 'wgy687@163.com', trunc(sysdate), 'IT_PROG', 20000, 102); insert into employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id) values (9999, 'wugy', 'wgy687@163.com', trunc(sysdate), 'IT_PROG', 20000, 102) Error at line 1 ORA-04091: 表 HR.EMPLOYEES 发生了变化, 触发器/函数不能读它 ORA-06512: 在 "HR.TRG_UPDATE_SALARY", line 3 ORA-06512: 在 "HR.TRG_UPDATE_SALARY", line 7 ORA-04088: 触发器 'HR.TRG_UPDATE_SALARY' 执行过程中出错 update employees set salary = 7000 where employee_id = 198; update employees set salary = 7000 where employee_id = 198 Error at line 1 ORA-04091: 表 HR.EMPLOYEES 发生了变化, 触发器/函数不能读它 ORA-06512: 在 "HR.TRG_UPDATE_SALARY", line 3 ORA-06512: 在 "HR.TRG_UPDATE_SALARY", line 7 ORA-04088: 触发器 'HR.TRG_UPDATE_SALARY' 执行过程中出错

 

這時就需要另一種方法了。

 

首先，需要創建一個包(Package)來保存員工的上司ID和員工的新工資。

create or replace package pkg_employees is type salaries is table of employees.salary%type index by binary_integer; type mgr_ids is table of employees.manager_id%type index by binary_integer; v_emp_salaries salaries; v_mgr_ids mgr_ids; v_num_entities binary_integer := 0; end pkg_employees;

 

然後，從新創建一個行級觸發器和語句級觸發器。

create or replace trigger trg_update_salary before update of salary or insert on employees for each row begin pkg_employees.v_num_entities := pkg_employees.v_num_entities + 1; -- 記錄每個更新的員工的工資和其上司的ID pkg_employees.v_emp_salaries(pkg_employees.v_num_entities) := :new.salary; pkg_employees.v_mgr_ids(pkg_employees.v_num_entities) := :new.manager_id; end; create or replace trigger trg_after_update_salary after update of salary or insert on employees declare cursor cur_manager_salary(mgr number) is select salary from employees where employee_id = mgr; begin -- 循環每個更新的員工 for idx in 1..pkg_employees.v_num_entities loop for ms in cur_manager_salary(pkg_employees.v_mgr_ids(idx)) loop -- 當上司的工資比其下屬小時，拋出一個自定義的錯誤 if ms.salary < pkg_employees.v_emp_salaries(idx) then raise_application_error(-20001, 'Employee''s salary cannot greater than his/her manager''s salary!'); end if; end loop; end loop; pkg_employees.v_num_entities := 0; -- 重置變量 end;

 

測試

update employees set salary = 7000 where employee_id = 198; Error at line 1 ORA-20001: Employee's salary cannot greater than his/her manager's salary! ORA-06512: 在 "HR.TRG_AFTER_UPDATE_SALARY", line 9 ORA-04088: 触发器 'HR.TRG_AFTER_UPDATE_SALARY' 执行过程中出错 update employees set salary = 5000 where employee_id = 198; 1 row updated. insert into employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id) values (9999, 'wugy', 'wgy687@163.com', trunc(sysdate), 'IT_PROG', 20000, 102); Error at line 1 ORA-20001: Employee's salary cannot greater than his/her manager's salary! ORA-06512: 在 "HR.TRG_AFTER_UPDATE_SALARY", line 9 ORA-04088: 触发器 'HR.TRG_AFTER_UPDATE_SALARY' 执行过程中出错 insert into employees (employee_id, last_name, email, hire_date, job_id, salary, manager_id) values (9999, 'wugy', 'wgy687@163.com', trunc(sysdate), 'IT_PROG', 7500, 102); 1 row created.

 

這樣就可以用觸發器作出這樣的約束了。