本文共 9400 字，大约阅读时间需要 31 分钟。

一、使用条件谓词

在上文中，看到了一个触发器，可以防止在周末插入EMPLOYEES：

CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT ON employees BEGIN IF TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN') THEN RAISE_APPLICATION_ERROR(-20500, 'You may insert into EMPLOYEES' ||' table only during business hours'); END IF;END;

假设希望在周末期间阻止EMPLOYEES上的任何DML操作，并为INSERT，UPDATE和DELETE提供不同的错误消息。 可以创建三个单独的触发器; 不过，也可以用一个触发器来做到这一点。

CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT OR UPDATE OR DELETE ON employeesBEGIN IF TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN') THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20501,'You may delete from EMPLOYEES' ||' table only during business hours'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20502,'You may insert into EMPLOYEES' ||' table only during business hours'); ELSIF UPDATING THEN RAISE_APPLICATION_ERROR (-20503,'You may update EMPLOYEES' ||' table only during business hours'); END IF;END IF;END;

可以使用条件谓词来测试特定列上的UPDATE：

CREATE OR REPLACE TRIGGER secure_empBEFORE UPDATE ON employeesBEGIN IF UPDATING('SALARY') THEN IF TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN') THEN RAISE_APPLICATION_ERROR (-20501,'You may update SALARY' ||' only during business hours'); END IF; ELSIF UPDATING('JOB_ID') THEN IF TO_CHAR(SYSDATE,'DY') = 'SUN' THEN RAISE_APPLICATION_ERROR (-20502,'You may not update JOB_ID on Sunday'); END IF;END IF;END;

二、了解行触发器

请记住，对于每个触发的DML语句，语句触发器只执行一次：

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employees BEGININSERT INTO log_emp_table (who, when) VALUES (USER, SYSDATE);END;

无论触发语句是更新一个员工，几个员工，还是根本没有员工，该触发器都会在日志表中正好插入一行。

假设您想为每个更新的员工在日志表中插入一行。 例如，如果更新了四名员工，则将四行插入日志表中。 你需要一个行触发器。

（1）行触发器触发序列

对于受触发DML语句影响的每一行，行触发器都会触发（执行）一次，无论是在处理该行之前还是仅在AFTER之后。 如果五个员工在部门50中，则行触发器执行五次：

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;

（2）创建行触发器

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWBEGININSERT INTO log_emp_table (who, when) VALUES (USER, SYSDATE);END;

    可以使用FOR EACH ROW来指定行触发器。 上一张幻灯片中的UPDATE语句现在将五行插入日志表中，每个EMPLOYEE行更新一行。但是，日志表中的所有五行都是相同的。 日志表不显示哪些员工已更新，或者他们的薪水有哪些变化。

（3）使用:OLD和：NEW限定符

    只有在行触发器中，您是否可以在当前正在更新的EMPLOYEES行中引用和使用旧列和新列值。     代码：OLD.column_name引用preupdate值，以及:NEW.column_name引用更新后的值。

例如，如果UPDATE语句将雇员的工资从10000更改为11000，则:OLD.salary的值为10000，以及:NEW.salary的值为11000.现可以将所需的数据插入日志记录表。

要记录employee_id，无论是否编码:OLD.employee_id或:NEW.employee_id，都无关紧要吗？有区别吗？

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWBEGININSERT INTO log_emp_table (who, when, which_employee, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.salary, :NEW.salary);END;

行触发器的第二个例子

CREATE OR REPLACE TRIGGER audit_emp_valuesAFTER DELETE OR INSERT OR UPDATE ON employeesFOR EACH ROWBEGIN INSERT INTO audit_emp(user_name, time_stamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);END;

第二个示例：测试audit_emp_values触发器

INSERT INTO employees(employee_id, last_name, job_id, salary, ...) VALUES (999, 'Temp emp', 'SA_REP', 1000,...);UPDATE employeesSET salary = 2000, last_name = 'Smith'WHERE employee_id = 999;

SELECT user_name, time_stamp, ... FROM audit_emp;

行触发器的第三个例子

假设你需要防止不是总裁或副总裁的雇员的工资超过15000美元。

CREATE OR REPLACE TRIGGER restrict_salaryBEFORE INSERT OR UPDATE OF salary ON employeesFOR EACH ROWBEGIN IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP')) AND :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn more than $15,000.'); END IF;END;

测试restrict_salary触发器：

UPDATE employees SET salary = 15500 WHERE last_name IN ('King','Davies');

King是（副）主席，但Davies不是。 此UPDATE语句会产生以下错误：

ORA-20202: Employee cannot earn more than $15,000.ORA-06512: at “USVA_TEST_SQL01_T01.RESTRICT_SALARY”, line 4ORA-04088: error during execution of trigger‘USVA_TEST_SQL01_T01.RESTRICT_SALARY’2. WHERE last_name IN (‘King’, ‘Davies’);

EMPLOYEES行都不会更新，因为UPDATE语句必须完全成功或根本不成功。

第四个例子：用触发器实现完整性约束

EMPLOYEES表在DEPARTMENTS表的DEPARTMENT_ID列中具有外键约束。 DEPARTMENT_ID 999不存在，因此此DML语句违反了约束条件，员工行未更新：

UPDATE employees SET department_id = 999WHERE employee_id = 124;

可以使用触发器自动创建新部门。 

第四个例子：创建触发器：

CREATE OR REPLACE TRIGGER employee_dept_fk_trgBEFORE UPDATE OF department_id ON employeesFOR EACH ROWDECLARE v_dept_id departments.department_id%TYPE;BEGIN SELECT department_id INTO v_dept_id FROM departments WHERE department_id = :NEW.department_id;EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO departments VALUES(:NEW.department_id, 'Dept '||:NEW.department_id, NULL, NULL);

来测试它：

UPDATE employees SET department_id = 999WHERE employee_id = 124;-- Successful after trigger is fired

三、使用REFERENCING子句

再看一下行触发器的第一个例子：

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWBEGIN INSERT INTO log_emp_table (who, when, which_employee, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.salary, :NEW.salary);END;

如果EMPLOYEES表的名称不同，该怎么办？

如果它被称为OLD呢？ OLD不是一个好名字，但是可能的。 我们的代码现在会是什么样子？

OLD现在意味着两件事：它是一个值限定符（如:NEW），也是一个表名。 该代码将起作用，但会令人困惑。 我们不需要

使用:OLD和:NEW。 我们可以通过包含REFERENCING子句来使用不同的限定符。

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON oldREFERENCING OLD AS former NEW AS latterFOR EACH ROWBEGIN INSERT INTO log_emp_table (who, when, which_employee, old_salary, new_salary) VALUES (USER, SYSDATE, :former.employee_id, :former.salary, :latter.salary);END;

FORMER和LATTER被称为关联名称。 他们是OLD和NEW的别名。 我们可以选择任何我们喜欢的相关名称（例如TOM和MARY），只要它们不是保留字。 REFERENCING子句只能用于行触发器。

四、使用WHEN子句

看看这个触发代码。 只有在新薪水高于旧薪水时才会记录薪资变化。

CREATE OR REPLACE TRIGGER restrict_salaryAFTER UPDATE OF salary ON employeesFOR EACH ROWBEGIN IF :NEW.salary > :OLD.salary THEN INSERT INTO log_emp_table (who, when, which_employee, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.salary, :NEW.salary); END IF;END;

整个触发器主体是一个单一的IF语句。 在现实生活中，这可能是许多代码行，包括CASE语句，循环和许多其他构造。 这将很难阅读。

可以在触发器标题中编写我们的IF条件，就在BEGIN子句之前。

CREATE OR REPLACE TRIGGER restrict_salaryAFTER UPDATE OF salary ON employeesFOR EACH ROWWHEN (NEW.salary > OLD.salary)BEGIN INSERT INTO log_emp_table (who, when, which_employee, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.salary, :NEW.salary);END;

这段代码更容易阅读，特别是如果触发器体长且复杂。 WHEN子句只能用于行触发器。

五、INSTEAD OF触发器

复杂视图（例如基于联接的视图）无法更新。 假设EMP_DETAILS视图是基于EMPLOYEES和DEPARTMENTS联合的复杂视图。 以下SQL语句失败：

INSERT INTO emp_details VALUES (9001,'ABBOTT',3000, 10, 'Administration');

可以通过创建一个触发器来直接更新两个基表，而不是尝试（和失败）更新视图。

INSTEAD OF触发器总是行触发器。

（1）一个INSTEAD OF触发器的例子

将INSERT执行到基于NEW_EMPS和NEW_DEPTS表的EMP_DETAILS视图中：

INSERT INTO emp_details VALUES (9001,'ABBOTT',3000, 10, 'Administration');

（2）创建一个INSTEAD OF触发器

步骤1：创建表格和复杂视图：

CREATE TABLE new_emps ASSELECT employee_id,last_name,salary,department_id FROM employees;CREATE TABLE new_depts ASSELECT d.department_id,d.department_name, sum(e.salary) dept_sal FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_id,d.department_name;CREATE VIEW emp_details ASSELECT e.employee_id, e.last_name, e.salary, e.department_id, d.department_name FROM new_emps e, new_depts d WHERE e.department_id = d.department_id;

第2步：创建INSTEAD OF触发器：

CREATE OR REPLACE TRIGGER new_emp_deptINSTEAD OF INSERT ON emp_detailsBEGIN INSERT INTO new_emps VALUES (:NEW.employee_id, :NEW.last_name, :NEW.salary, :NEW.department_id); UPDATE new_depts SET dept_sal = dept_sal + :NEW.salary WHERE department_id = :NEW.department_id;END;

（3）行触发器重访

看看这个行触发器，记录员工的工资变化：

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWBEGININSERT INTO log_table (employee_id, change_date, salary) VALUES (:OLD.employee_id, SYSDATE, :NEW.salary);END;

如果有一百万名员工，并且你给每个员工5％的工资增长：

UPDATE employees SET salary = salary * 1.05;

行触发器将自动执行一百万次，每次插入一行。 这将非常缓慢。

在课程的前期，学习了如何使用批量绑定（FORALL）来加速DML。 我们可以在我们的触发器中使用FORALL吗？

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWDECLARE TYPE t_log_emp IS TABLE OF log_table%ROWTYPE INDEX BY BINARY_INTEGER; log_emp_tab t_log_emp;BEGIN ... Populate log_emp_tab with employees’ change dataFORALL i IN log_emp_tab.FIRST..log_emp_tab.LAST INSERT INTO log_table VALUES log_emp_tab(i);END;

这不起作用。 为什么不？ 提示：请记住，这是一个行触发器，并考虑LOG_EMP_TAB收集变量的作用域。

CREATE OR REPLACE TRIGGER log_empsAFTER UPDATE OF salary ON employeesFOR EACH ROWDECLARE TYPE t_log_emp IS TABLE OF log_table%ROWTYPE INDEX BY BINARY_INTEGER; log_emp_tab t_log_emp;BEGIN ... Populate log_emp_tab with employees’ change dataFORALL i IN log_emp_tab.FIRST..log_emp_tab.LAST INSERT INTO log_table VALUES log_emp_tab(i);END;

在触发器的每次执行结束时，触发器变量会丢失范围。 所以每次触发行触发器时，LOG_EMP_TAB中已收集的所有数据都将丢失。为了避免丢失这些数据，我们需要一个只触发一次的触发器 - 一个语句触发器。 但要引用每行的列值（使用：OLD和：NEW），我们需要一个行触发器。

但是单个触发器不能同时是行触发器和语句触发器。 对？ 错误！ 我们创建一个复合触发器。

六、什么是复合触发器？

一个触发器，可以包含针对每个可能的时间点的操作：触发语句之前，每行之前，每行之后，触发语句之后。 复合触发器有一个声明部分，以及每个时间点的部分。 你不必包含所有的时间点，只需要你需要的时间点。 复合触发器变量的范围是整个触发器，因此它们在整个执行过程中保留其范围。

（1）复合触发结构

（2）例子

这个例子有一个声明部分和四个可能的时间点部分中的两个。

（3）完整代码

CREATE OR REPLACE TRIGGER log_empsFOR UPDATE OF salary ON employeesCOMPOUND TRIGGERDECLARE TYPE t_log_emp IS TABLE OF log_table%ROWTYPE INDEX BY BINARY_INTEGER; log_emp_tab t_log_emp; v_index BINARY_INTEGER := 0;AFTER EACH ROW IS BEGIN v_index := v_index + 1; log_emp_tab(v_index).employee_id := :OLD.employee_id; log_emp_tab(v_index).change_date := SYSDATE; log_emp_tab(v_index).salary := :NEW.salary;END AFTER EACH ROW;AFTER STATEMENT IS BEGINFORALL I IN log_emp_tab.FIRST..log_emp_tab.LAST INSERT INTO log_table VALUES log_emp_tab(i);END AFTER STATEMENT;END log_emps;

转载地址：https://maokun.blog.csdn.net/article/details/80536013 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！