PL/SQL 基础---触发器

PL/SQL 基础—触发器

对数据库对象的操作可引发很多事件，比如before Insert,before update 等等，但这些事件产生的时候我们可以写响应代码来完成一些基于事件的操作，通常这些操作被写成一段Plsql程序；那么这些更具体的数据库对象上的事件相关的程序呢就称为数据库Trigger；

实际使用中，除非迫不得已，尽量避免使用Trigger,因为这会导致维护困难；

定义触发器的关键因素

因素	内容
时机	Before 或者 After 或 Instead of
事件	Insert 或 Update 或 Delete
对象	表名（或视图名）
类型	Before 或者 After 或 Instead of
条件	满足特定Where条件才执行
内容	通常是一段PLSQL块代码

重点注意：
Instead of : 用Trigger的内容替换 事件本身的动作
Row级： SQL语句影响到的每一行都会引发Trigger
Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）

Trigger的触发顺序

DML语句

INSERT INTO departments (department_id,
    department_name, location_id)
VALUES (400, 'CONSULTING', 2400);

BEFORE statement trigger
BEFORE row trigger
400	CONSULTING	2400
AFTER row trigger
AFTER statement trigger

常见触发器类型

--单一事件触发器
CREATE OR REPLACE TRIGGER tri_secure_emp
  BEFORE INSERT ON EMPLOYEES
BEGIN
  IF (to_char(SYSDATE, 'DY') IN ('星期五')) THEN
    RAISE_APPLICATION_ERROR(-20050, '触发器触发');
  END IF;
END;


--多事件触发器
CREATE OR REPLACE TRIGGER tri_secure_emp
 BEFORE INSERT OR DELETE OR UPDATE ON EMPLOYEES

BEGIN
  IF (to_char(SYSDATE, 'DY') IN ('星期五')) THEN
    IF DELETING THEN 
       RAISE_APPLICATION_ERROR(-20050, 'NO DETETING...');
    ELSIF INSERTING THEN 
       RAISE_APPLICATION_ERROR(-20050, 'NO INSERTING...');
    ELSIF UPDATING THEN
       RAISE_APPLICATION_ERROR(-20050, 'NO UPDATING...');
    ELSE
       RAISE_APPLICATION_ERROR(-20050, 'NOTHING...');
    END IF;
  END IF;
END;


-- 行级触发器
CREATE OR REPLACE TRIGGER tri_multi_restrict
    BEFORE INSERT ON EMPLOYEES 
  FOR EACH ROW
BEGIN
  :NEW.LAST_NAME := :NEW.LAST_NAME || '_NEW';
END;


-- WHEN 限制触发条件
CREATE OR REPLACE TRIGGER derive_commission_pct
  BEFORE INSERT OR UPDATE OF salary ON employees
  FOR EACH ROW
  WHEN (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.commission_pct := :OLD.commission_pct + 0.05;
  END IF;
END;

--INSTEAD OF触发器（只能建立在视图上）
CREATE OR REPLACE TRIGGER tri_instead_of
    INSTEAD OF INSERT OR UPDATE ON VIEW_NAME
  FOR EACH ROW
BEGIN
  NULL;
END;

NEW 和 OLD修饰词

对于数据库更新操作（UPDATE， INSERT），可以通过NEW / OLD关键词获取修改后的数据和修改前的数据，对于INSERT操作可通过NEW关键词获取新插入的数据；

注：只有BEFORE类型触发器才能对NEW进行赋值

-- NEW OLD
CREATE OR REPLACE TRIGGER tri_multi_restrict
    BEFORE INSERT OR UPDATE ON EMPLOYEES 
  FOR EACH ROW
BEGIN
  dbms_output.put_line(:NEW.LAST_NAME);
  dbms_output.put_line(:OLD.LAST_NAME);
END;

附

数据库触发器

除了针对Table,View的Trigger, 还有针对Database的 Trigger；

-- 登录记录
CREATE OR REPLACE TRIGGER logon_trig
  AFTER LOGON ON SCHEMA
BEGIN
  INSERT INTO log_trig_table
    (user_id, log_date, action)
  VALUES
    (USER, SYSDATE, 'Logging on');
END;