数据库系统之数据库触发器

什么是Database trigger？

数据库触发器是存储在数据字典中的一段代码，每当发生预定义的事件并满足预定义的条件时，它都会自动处理。
打个比方，我们想为所有工资超过100000的员工自动提高工作级别：

ON UPDATE OF EMPLOYEE.salary
IF :NEW.salary > 100000 THEN
IncreaseJobLevel(:NEW.enumber, :NEW.salary);
END IF;

例如，我们想实现一个数据安全规则，即工资不能在周末被修改。

ON UPDATE OF EMPLOYEE.salary
IF TO_CHAR(SYSDATE,'Day') IN ('Saturday', 'Sunday') THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be updated over a weekend !');
END IF;

例如，我们想要强制一个一致性约束，即一个部门不能有超过100名员工:

ON INSERT INTO EMPLOYEE
SELECT COUNT(*)
INTO total_employees
FROM EMPLOYEE
WHERE dname = :NEW.dname;
IF total_employees = 100 THEN
RAISE_APPLICATION_ERROR(-20002, 'Too many employees in ' || :NEW.dname);
END IF;

在上面的例子中，我们假设一个触发器在INSERT语句之前被处发。
有时，在修改关系表之后和COMMIT语句之前触发一个触发器来验证一致性约束更为方便

这就是为什么我们有两个触发器的时间选项：BEFORE和 AFTER。

主动数据库系统

主动数据库系统是一种能够检测一定时间内发生的事件，并在对这些事件的响应中，如果在满足预定义条件时就进行执行动作的系统。
主动数据库系统的逻辑实现为下面事件的集合：
Event-Condition-Action (ECA)rules
在SQL中，可以用CREATE触发器语句创建ECA规则，也可以用DROP触发器语句删除ECA规则

CREATE OR REPLACE TRIGGER 语句可以实现ECA规则。
举个例子：

--event
ON UPDATE OF EMPLOYEE.salary
--condition
IF :NEW.salary > 100000
--action
IncreaseJobLevel(:NEW.enumber, :NEW.salary);

CREATE OR REPLACE TRIGGER 声明

结合代码说明：

CREATE OR REPLACE TRIGGER CheckBudget
--时间的选择
BEFORE
--事件
UPDATE OF budget ON DEPARTMENT
--触发器的类型，可以是语句触发器，也可以是行触发器
FOR EACH ROW -- FOR EACH ROW 意味着它是一个行触发器
--条件
WHEN NEW.name = 'Math' --NEW 被称作 pseudorecord
--触发器的主体开始
BEGIN
--Pseudorecords ：OLD and :NEW代表了一行被删除或修改之前和一行被修改或者插入之后
IF NOT ( :NEW.budget BETWEEN 1 AND 7000 ) THEN
--发器与触发该触发器的事务一起异常终止
RAISE_APPLICATION_ERROR(-200001, 'Budget of department ' || :NEW.name ||
                                 ' cannot be equal to ' || :NEW.budget );

--触发器的主体结束
END IF;
END;

时间的选择有以下情况：
BEFORE 触发器在触发事件之前触发。

AFTER 触发器在触发事件后触发。

INSTEAD OF 触发器触发而不是触发事件，它通常用于正确实现视图更新操作，即通过在关系视图上形成的更新来正确修改基本关系表。

举例：

--1
BEFORE UPDATE OF budget ON DEPARTMENT
--2
AFTER DELETE OR UPDATE ON DEPARTMENT
--3
INSTEAD OF INSERT ON EMPVIEW

Statement database trigger

一个示例:

CREATE OR REPLACE TRIGGER ModifyDepartment
AFTER DELETE OR UPDATE ON DEPARTMENT
BEGIN -- Statement triggers have no FOR EACH ROW clause!
IF DELETING THEN
INSERT INTO DEPTAUDIT VALUES('DELETE', SYSDATE);
ELSIF UPDATING THEN
INSERT INTO DEPTAUDIT VALUES('UPDATE', SYSDATE);
END IF;
END;

Row database triggers

行触发器既可以在触发事件影响关系表中的一行之后，也可以在触发事件之前。
行触发器示例：

CREATE OR REPLACE TRIGGER UpdateDepartment
AFTER UPDATE ON DEPARTMENT
FOR EACH ROW -- 行触发器必须包含FOR EACH ROW 
WHEN (NEW.city = 'Boston')--仅用于行触发器!
BEGIN
INSERT INTO DEPTTRACE VALUES
                ('UPDATE', SYSDATE, :NEW.name, :NEW.budget, :NEW.city,
                                    :OLD.name, :OLD.budget, :OLD.city );
END;

References

1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 8.3 Triggers, Pearson Education Ltd, 2015.