触发器
定义
Trigger是与tbale，view，schema或者databse相关的plsql程序块或过程，当特定事件发生时自动触发执行。
分类
触发器主要有两种类型：
- 应用trigger：由特定的应用程序的事件触发
- 数据库trigger：由数据库的特定事件触发
设计Trigger触发器的基本指导
- 首先，使用触发器可以执行一些相关动作，这些相关动作是集中的全局操作
- 其次，使用trigger应避免和数据库已有功能重复，或者和其他触发器重复
- 再其次，触发器可以调用plsql过程
- 最后，过度使用触发器会导致大型应用系统代码难以维护
创建DML触发的triggers
触发时机
触发时机有3个：
- BEFORE：DML动作发生之前触发
- AFTER：DML动作发生之后触发
- INSTEAD OF：替换原有动作，比如一些不可更新的视图
单行受影响的情况
假设DML语句
INSERT INTO departments
(department_id, department_name, location_id)
VALUES(400, 'CONSULTING', 2400);
s_line.JPG
多行受影响的情况
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;
m_line.JPG
语法
CREATE [OR REPLACE ] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]]
trigger_body
触发顺序
注意，如果一个事件触发多个触发器，则这个触发的先后顺序是随机的。
触发事件和trigger body
触发事件是指由何种DML语句来触发，包括INSERT、UPDATE [OF column]和DELETE。
Trigger Body是指触发事件发生后，执行的动作，可以是plsql block也可以调用过程。
创建语句级和行级triggers
DML 触发器的类型
DML 触发器主要有两类：
- 语句级
- 针对触发事件执行一次
- 默认类型
- 即便没有处理数据行仍然触发
- 行级
- 收到事件影响的每行都会触发一次
- 如果没有数据行受到影响则不触发
- 通过FOR EACH ROW语句来指定
创建DML语句Trigger
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR (TO_CHAR(SYSDATE, 'HH24:MI')
NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLCATION_ERROR(-20500, 'You may insert'
|| ' into EMPLOYEES table only during ' || ' business hours.');
END IF;
END;
dml_tri.JPG
- 测试
INSERT INTO employees(employee_id, last_name,
first_name, email, hire_date,
job_id, salary, department_id)
VALUES(300, 'Smith', 'Rob', 'RSMITH',
SYSDATE, 'IT_PROG', 4500, 60);
error.JPG
使用条件判断
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR (TO_CHAR(SYSDATE, 'HH24')
NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN RAISE_APPLCATION_ERROR(-20502, 'You may delete'
|| ' from EMPLOYEES table only during ' || ' business hours.');
ELSIF INSERTING THEN RAISE_APPLCATION_ERROR(-20500, 'You may insert'
|| ' into EMPLOYEES table only during ' || ' business hours.');
ELSIF UPDATING('SALARY') THEN RAISE_APPLCATION_ERROR(-20503, 'You may update salary only during ' || ' business hours.');
ELSE RAISE_APPLCATION_ERROR(-20504, 'You may update salary only during ' || ' normal hours.');
END IF;
END IF;
END;
创建DML行级Trigger
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FROM EACH ROW
BEGIN
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;
/
使用OLD和NEW限定符
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
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.employees_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary);
END;
/
实例
CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
WHERE (NEW.job_id = 'SA_REP')
BEGIN
IF INSERTING THEN
:NEW.commission_pct := 0;
ELSIF :OLD.commission_pct IS NULL THEN
:NEW.commission_pct := 0
ELSE
:NEW.commisson_pct := OLD.commission_pct+0.05;
END IF;
END;
/
触发模式总结
- 执行所有的BEFORE STATEMENT trigger。
- 依据受影响的行进行循环：
- 执行所有的BEFORE ROW trigger
- 执行DML语句并执行行约束检查
- 执行所有AFTER ROW trigger
- 执行所有AFTER STATEMENT trigger
服务于约束的Trigger
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER employee_dept_fk_trg
AFTER UPDATE OF department_id
ON employees FOR EACH ROW
BEGIN
INSERT INTO departments
VALUES(:new.department_id, 'Dept ' || :new.department_id, NULL, NULL);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL; -- mask exception if department exists
END;
/
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
--- Successful after trigger is fired.
INSTEAD OF Triggers
instead.JPG
创建INSTEAD OF Trigger
create_instead.JPG
可以用于针对复杂视图的DML操作，例如
CREATE TABLE new_emps AS
SELECT employee_id, last_name, salary, department_id
FROM employees;
CREATE TABLE new_depts AS
SELECT d.department_id, d.departmant_name, sum(e.salary) dept_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id;
CREATE VIEW emp_details AS
SELECT e.employee_id, e.last_name, e.salary
e.department_id, e.department_name
FROM employees e, department d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
Trigger 和 procedure的比较
| trigger | Procedure |
|---|---|
用CREATE TRIGGER定义 |
用CREATE PROCEDURE定义 |
| 源代码存储在数据字典 USER_TRIGGERS中 | 源代码存储在数据字典 USER_SOURCE中 |
| 被DMS隐式触发 | 被DMS显式触发 |
| 不能有COMMIT、SAVEPOINT和ROLLBACK | 可以有COMMIT、SAVEPOINT和ROLLBACK |
管理triggers
- 启用或者禁用database trigger
ALTER TRIGGER trigger_name DISABLE|ENABLE
- 启用或者禁用针对特定表的trigger
ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS
- 重新编译 trigger
ALTER TRIGGER trigger_name COMPILE
测试Trigger
- 测试触发事件和非触发事件
- 测试WHEN语句的每个用例
- 测试直接的语句触发和通过过程中的语句触发
- 测试trigger对其他trigger的影响
- 测试其他trigger对trigger的影响
