6.11数据库触发器

1、数据库触发器 Trigger的概念

对数据库对象的操作可引发很多事件，比如before Insert，before update 等等，但这些事件产生的时候可以写响应代码来完成一些基于事件的操作，通常这些操作被写成一段Plsql程序；那么这些更具体的数据库对象上的事件相关的程序呢就称为数据库Trigger
实际应用场景举例（从EBS的发运确认产生的MMT 生成 POS收货确认单）：

注意：除非迫不得已，尽量避免使用Trigger，因为这会导致维护困难

2、创建Trigger

Trigger的定义语句里面涉及到如下关键因素：

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

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

3、Trigger的触发顺序

A.创建Statement 级别Trigger 语法

1,语法

CREATE [OR REPLACE] TRIGGER trigger_name
   timing
   event1 [OR event2 OR event3]
   ON table_name
trigger_body

2,举例

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_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.');
   END IF;
END;
/

B.多事件Trigger，在Trigger Body中判断具体事件

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE 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_APPLICATION_ERROR (-20502,'You may delete from EMPLOYEES
 table only during business hours.');
 ELSIF INSERTING THEN
 RAISE_APPLICATION_ERROR (-20500,'You may insert into
 EMPLOYEES table only during business hours.');
 ELSIF UPDATING ('SALARY') THEN
 RAISE_APPLICATION_ERROR (-20503,'You may update
 SALARY only during business hours.');
 ELSE
 RAISE_APPLICATION_ERROR (-20504,'You may update
 EMPLOYEES table only during normal hours.');
 END IF;
 END IF;
END;

C.创建Row级别Trigger 语法

1,语法

CREATE [OR REPLACE] TRIGGER trigger_name
 timing
 event1 [OR event2 OR event3]
 ON table_name
 [REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
 [WHEN (condition)]
trigger_body

2,举例

CREATE OR REPLACE TRIGGER restrict_salary
 BEFORE INSERT OR UPDATE OF salary ON employees
 FOR 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 this amount');
 END IF;
END;
/

4、使用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_table (user_name, timestamp,
 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;
/

5、使用WHEN限制Trigger的触发条件

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;
/

6、INSTEAD OF Trigger的运行原理图解

INSTEAD OF Trigger的使用场景：建立在View上的Trigger 通常是INSTEAD OF类型的，因为复杂视图不能被 直接更改； 意义不大，很少使用；

7、管理Trigger

A.失效/生效

ALTER TRIGGER trigger_name DISABLE | ENABLE

B.批量失效/生效

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

C.重新编译

ALTER TRIGGER trigger_name COMPILE

D.删除

DROP TRIGGER trigger_name;

8、约束与Trigger同时存在，那个先执行？

UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Integrity constraint violation error
CREATE OR REPLACE TRIGGER constr_emp_trig
AFTER UPDATE ON employees
 FOR EACH ROW
BEGIN
 INSERT INTO departments
 VALUES (999, 'dept999', 140, 2400);
END;
/
UPDATE employees SET department_id = 999
WHERE employee_id = 170;
-- Successful after trigger is fired

9、Database的 Trigger

除了针对Table，View的Trigger，还有针对Database的 Trigger，比如 ：写Triger记录所有登录/登出数据库的用户

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;
/

CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging off');
END;
/

10、冲突表(Mutating Table)

当某张表上的针对DML动作的Trigger需要访问到表自身的数据时，对Trigger来说，这就是一张冲突表
对于employee表的新增或者更改Salary动作引发的Trigger，该Trigger又要读取Salary的Min值，这就冲突了。

CREATE OR REPLACE TRIGGER check_salary
 BEFORE INSERT OR UPDATE OF salary, job_id
 ON employees
 FOR EACH ROW
 WHEN (NEW.job_id <> 'AD_PRES')
DECLARE
 v_minsalary employees.salary%TYPE;
 v_maxsalary employees.salary%TYPE;
BEGIN
 SELECT MIN(salary), MAX(salary)
 INTO v_minsalary, v_maxsalary
 FROM employees
 WHERE job_id = :NEW.job_id;
 IF :NEW.salary < v_minsalary OR
 :NEW.salary > v_maxsalary THEN
 RAISE_APPLICATION_ERROR(-20505,
'Out of range');
 END IF;
END;
/

11、将Trigger用于数据变更审计日志

Oracle内置数据更改审计的功能： 即更改动作可以被记录到审计记录表 sys.Audit$ .
要让审计功能其作用，必须：
1、设置数据库参数：audit_trail = DB 或者 OS （默认是NONE)
2、设置要审计的对象：比如

AUDIT INSERT, UPDATE, DELETE
 ON stu3693.copy_emp
 BY ACCESS
WHENEVER SUCCESSFUL;

通常情况下，客户方的DBA从性能角度考虑，不愿意启用数据库的审计功能，但某些客户认为某张表的变更记录 非常重要，一定要有审计功能可考虑使用Trigger作为替代方案，比如：

CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF (audit_emp_package.g_reason IS NULL) THEN
 RAISE_APPLICATION_ERROR (-20059, 'Specify a reason
 for the data operation through the procedure SET_REASON
 of the AUDIT_EMP_PACKAGE before proceeding.');
ELSE
 INSERT INTO audit_emp_table (user_name, timestamp, id,
 old_last_name, new_last_name, old_title, new_title,
 old_salary, new_salary, comments)
 VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name,
 :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary,
 :NEW.salary, audit_emp_package.g_reason);
END IF;
END;