Oracle SQL 学习笔记26 - 触发器

触发器

定义

Trigger是与tbale，view，schema或者databse相关的plsql程序块或过程，当特定事件发生时自动触发执行。

分类

触发器主要有两种类型：

1. 应用trigger：由特定的应用程序的事件触发
2. 数据库trigger：由数据库的特定事件触发

设计Trigger触发器的基本指导

• 首先，使用触发器可以执行一些相关动作，这些相关动作是集中的全局操作
• 其次，使用trigger应避免和数据库已有功能重复，或者和其他触发器重复
• 再其次，触发器可以调用plsql过程
• 最后，过度使用触发器会导致大型应用系统代码难以维护

创建DML触发的triggers

触发时机

触发时机有3个：

1. BEFORE：DML动作发生之前触发
2. AFTER：DML动作发生之后触发
3. 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 触发器主要有两类：

1. 语句级

• 针对触发事件执行一次
• 默认类型
• 即便没有处理数据行仍然触发

2. 行级

• 收到事件影响的每行都会触发一次
• 如果没有数据行受到影响则不触发
• 通过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;
/

触发模式总结

1. 执行所有的BEFORE STATEMENT trigger。
2. 依据受影响的行进行循环：

• 执行所有的BEFORE ROW trigger
• 执行DML语句并执行行约束检查
• 执行所有AFTER ROW trigger

3. 执行所有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的影响