DB2 - 触发器

Db2 - 触发器

本章介绍触发器，这是Db2中一个强大的功能，用于响应数据修改事件并自动执行操作。

触发器介绍

触发器是一个命名的数据库对象，包含一组SQL语句，当表或视图上发生特定事件时，这些语句会自动执行或“触发”。这些事件是数据修改操作：INSERT（插入）、UPDATE（更新）或DELETE（删除）。

触发器存储在数据库中并由数据库服务器管理。这确保了无论哪个应用程序或用户执行数据修改，定义的业务规则都能得到一致的执行。它们通常用于强制执行复杂的业务规则、维护数据完整性和创建审计跟踪。

触发器类型

触发器主要根据其相对于触发事件的激活时间进行分类：

BEFORE 触发器：这些触发器在INSERT、UPDATE或DELETE操作应用于数据库之前触发。它们非常适合在数据写入之前验证或修改传入数据。例如，您可以使用BEFORE触发器自动为新行设置created_at时间戳。
AFTER 触发器：这些触发器在操作成功完成之后触发。它们用于因数据更改而应发生的动作，例如将更改记录到审计表或更新汇总表。
INSTEAD OF 触发器：这是一种定义在视图而非表上的特殊类型触发器。它们替代触发的INSERT、UPDATE或DELETE语句触发。它们提供了一种机制，通过定义正确的逻辑来修改底层基表，从而使不可更新的视图（例如，基于连接的视图）变得可更新。

触发器的替代方案：标识列

在旧系统中，触发器一个非常常见的用例是从序列生成唯一的PRIMARY KEY值。现代Db2为此提供了更简单、更高效的内置机制：标识列（Identity Columns）。

-- 这是自动递增键的推荐现代方法。
CREATE TABLE products (
    product_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    product_name VARCHAR(100),
    PRIMARY KEY (product_id)
);

-- Db2 会自动生成 ID。无需触发器！
INSERT INTO products (product_name) VALUES ('Super Widget');

对于自动递增键，您应该始终优先使用IDENTITY列而不是BEFORE INSERT触发器。

创建 BEFORE 触发器：数据验证

让我们创建一个BEFORE触发器，以确保员工的薪资永远不能设置为负值。

CREATE TRIGGER check_positive_salary
NO CASCADE BEFORE UPDATE ON employees
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.salary < 0)
BEGIN
    -- 发出错误信号以阻止更新
    SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'Salary cannot be negative.';
END;

现在，如果用户尝试设置负工资，触发器将在更新发生之前触发，并且SIGNAL语句将引发错误，导致整个操作失败。

创建 AFTER 触发器：审计更改

AFTER触发器的一个经典用途是创建审计跟踪。让我们创建一个触发器，将员工的每次薪资更改记录到单独的salary_audit表中。

-- 首先，创建审计表
CREATE TABLE salary_audit (
    audit_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    changed_by VARCHAR(128),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 现在，创建 AFTER UPDATE 触发器
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN
    -- 向审计表插入包含旧值和新值的记录
    INSERT INTO salary_audit(employee_id, old_salary, new_salary, changed_by)
    VALUES (o.employee_id, o.salary, n.salary, SESSION_USER);
END;

有了这个触发器，employees表中salary列的每次UPDATE操作都将自动生成相应的审计记录。

删除触发器

删除触发器会将其从数据库中永久移除。

语法：

DROP TRIGGER <trigger_name>;

示例：

DROP TRIGGER log_salary_change;

最佳实践和常见陷阱

保持逻辑简单：触发器应包含简单、快速执行的逻辑。复杂的业务逻辑通常更适合放在存储过程中。
警惕性能：触发器会给表上的每次DML操作增加开销。过度使用或低效的触发器逻辑会严重降低数据库性能。
避免级联触发器：创建导致其他触发器触发的触发器时要谨慎，这可能导致复杂、难以调试的事件链（级联触发器）甚至无限循环（递归触发器）。
彻底文档化：由于触发器是隐式执行的，它们的存在可能不明显。务必对您的触发器及其目的进行彻底的文档化。