Posted by on | Featured

如果你曾经需要让自己的数据库能够自动响应各种变化——比如记录对敏感表的每一次更新操作，在插入数据之前执行某些业务规则，或者在删除数据后同步相关派生数据——那么触发器正是你所需要的工具。

数据库触发器是一种函数，当表上发生特定事件时，数据库会自动执行这个函数。你不需要手动调用它；只需定义相应的条件，剩下的工作就由数据库来完成。

在本教程中，你将了解什么是触发器、它们是如何工作的、何时应该使用它们，以及何时应该避免使用它们。我们会通过PostgreSQL的实际例子来进行讲解，但这些核心概念同样适用于大多数关系型数据库。

目录

先决条件

为了能够跟随示例进行学习，你需要具备以下条件：

  • 基本的SQL知识（SELECT、INSERT、UPDATE、DELETE操作)

  • 一个正在运行的PostgreSQL实例（版本12或更高版本）

  • 一个SQL客户端工具，例如psql、pgAdmin或DBeaver

如果你还没有安装PostgreSQL，也可以使用Neon或Supabase等平台提供的免费云托管实例来进行学习。

触发器的工作原理

从宏观角度来看，触发器由三个部分组成：

  1. 事件：是什么操作触发了触发器的执行（INSERT、UPDATE、DELETE或TRUNCATE）

  2. 执行时机：触发器相对于该事件在何时被执行（BEFORE或AFTER）

  3. 处理逻辑

      4. 具体流程如下：用户或应用程序对表进行操作后，数据库会检查是否有关联的触发器；如果找到匹配项，数据库就会自动执行相应的触发器函数。

      你可以将触发器视为数据库的事件监听器。就像JavaScript中的`addEventListener`用于检测点击或按键操作一样，数据库触发器也会监视表中行级别的变化。

      如何创建你的第一个触发器

      在PostgreSQL中，创建触发器需要两个步骤：首先创建一个触发器函数，然后使用`CREATE TRIGGER`语句将该函数关联到某个表上。

      我们来举个具体的例子。假设你有一个`products`表，并且希望每当有行被修改时，都能自动更新`updated_at`时间戳。

      步骤1 – 创建表

      CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

      步骤2 – 创建触发器函数

      在PostgreSQL中，触发器函数是一种特殊的函数，它的返回类型为`TRIGGER`。在函数体内，你可以访问两个重要的变量：`NEW`（操作后的行）和`OLD`（操作前的行）。

      CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

      这个函数每次被执行时，都会将`updated_at`列设置为当前时间戳。然后它返回`NEW`，这样PostgreSQL就会继续处理这条已被修改的行。

      步骤3 – 将触发器关联到表上

      CREATE TRIGGER trigger_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

      让我们来分析这条语句的每个部分：

      • BEFORE UPDATE – 触发器会在更新操作被应用到表之前被执行

      • ON products – 这个触发器与`products`表相关联

      • FOR EACH ROW – 对于每一行受到更新影响的记录，这个函数都会被执行一次

      • EXECUTE FUNCTION set_updated_at() – 要调用的函数

      步骤4 – 进行测试

      INSERT INTO products (name, price) VALUES ('Wireless Keyboard', 49.99);

-- 等一会儿，然后更新这条记录
UPDATE products SET price = 44.99 WHERE name = 'Wireless Keyboard';

SELECT name, price, created_at, updated_at FROM products;

      你会看到，即使没有在查询中明确设置`updated_at`字段，它的值也会自动被更新为更新操作发生的时间。这就是触发器在发挥作用。

      触发器：执行前与执行后

      触发器的执行时机决定了它相对于实际数据变更而言会在何时被调用。

      执行前的触发器会在行被插入、更新或删除之前被执行。当你需要修改或验证传入的数据时，这类触发器非常有用。由于此时数据变更尚未生效，因此你可以修改NEW字段中的值，甚至可以通过返回NULL来完全取消该操作。

      执行后的触发器则会在行变更被正式保存到数据库中之后被执行。它们适用于执行日志记录、发送通知或更新相关表格等操作。此时数据变更已经完成，因此你无法再修改这些数据，但可以查看OLD和NEW字段的内容，从而了解发生了哪些变化。

      一个简单的经验法则是：当你需要修改或拒绝数据时，使用执行前的触发器；而当你需要对已经完成的操作做出响应时，则使用执行后的触发器。

      如何利用执行后触发器构建审计日志

      触发器最常见的用途之一就是用于生成审计日志，即记录对重要表格所进行的每一项变更。让我们来实际操作一下吧。

      步骤1 – 创建审计表

      CREATE TABLE product_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    action VARCHAR(10) NOT NULL,
    old_price NUMERIC(10, 2),
    new_price NUMERIC(10, 2),
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMP DEFAULT NOW()
);

      步骤2 – 创建审计触发器函数

      CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO product_audit (product_id, action, old_price, new_price)
        VALUES (OLD.id, 'UPDATE', OLD.price, NEW.price);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO product_audit (product_id, action, old_price)
        VALUES (OLD.id, 'DELETE', OLD.price);
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO product_audit (product_id, action, new_price)
        VALUES (NEW.id, 'INSERT', NEW.price);
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

      这里有几个重要的细节需要注意。TG_OP是一个特殊的字符串，PostgreSQL在触发器函数中提供了这个变量。它用于指示是哪种操作触发了该触发器：'INSERT'、'UPDATE'还是'DELETE'。这样，我们就可以用同一个函数来处理不同类型的操作了。

      函数结尾处的RETURN COALESCE(NEW, OLD)确保了函数能够返回正确的数据。对于插入和更新操作，NEW字段存在且其值会被返回；而对于删除操作，NEW为NULL，因此会返回OLD字段的值。

      步骤 3 – 绑定触发器

      CREATE TRIGGER trigger_product_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

      注意AFTER INSERT OR UPDATE OR DELETE这个语法。你可以将一个触发器绑定到多个操作上，这样可以使你的代码结构更加清晰。

      步骤 4 – 进行测试

      -- 插入一条新的产品记录
INSERT INTO products (name, price) VALUES ('USB-C Hub', 29.99);

-- 更新价格
UPDATE products SET price = 24.99 WHERE name = 'USB-C Hub';

-- 删除该产品记录
DELETE FROM products WHERE name = 'USB-C Hub';

-- 查看审计日志
SELECT * FROM product_audit ORDER BY changed_at;

      在product_audit表中，你会看到三条记录（每条记录对应一次操作），其中自动记录了产品价格的变化情况。完全不需要编写任何应用程序代码。

      如何使用BEFORE触发器进行验证

      触发器也可以在数据库层面强制执行业务规则。比如，如果你想防止任何产品的价格变为负数，就可以使用触发器来实现这一目标。

      CREATE OR REPLACE FUNCTION prevent_negative_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION '产品价格不能为负数。检测到的价格为：%', NEW.price;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_price();

      现在来测试一下：

      INSERT INTO products (name, price) VALUES ('Faulty Item', -10.00);
-- 错误：产品价格不能为负数。检测到的价格为：-10.00

      这条插入语句会被完全拒绝，该记录永远不会被添加到表中。这种机制非常强大，因为无论是由哪个应用程序或脚本发送查询，这个规则都会在数据库层面得到执行。

      行级触发器与语句级触发器

      到目前为止，你看到的所有触发器都使用了FOR EACH ROW选项，这意味着该触发函数会针对每一条受影响的记录分别执行一次。如果你在一条查询中更新了100条记录，那么触发函数就会被执行100次。

      PostgreSQL还支持FOR EACH STATEMENT类型的触发器，这种触发器会在每条SQL语句执行完毕后被调用一次，而与实际影响了多少条记录无关。

      CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE '对products表进行了批量操作';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER triggerbulk_update_notice
AFTER UPDATE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

      语句级触发器虽然使用得不多，但它们在某些场景下非常有用。例如，在批量更新数据后，使用语句级触发器可以一次性发送通知，而无需为每一条被更新的记录都单独发送通知。

      重要提示：在语句级触发器中，NEW和OLD变量是不可用的，因为这类触发器并不与任何特定的行相关联。

      关于NEW和OLD变量的说明

      以下是关于在行级触发器中何时可以使用NEW和OLD变量的简要说明：

      操作类型 OLD变量 NEW变量
      INSERT 不可用 包含新插入的行
      UPDATE 包含修改前的行 包含修改后的行
      DELETE 包含被删除的行 不可用

      了解这些变量的使用时机，可以帮助您避免在触发器函数中遇到运行时错误。

      如何管理触发器

      当您在数据库中添加更多触发器时，就需要掌握如何检查、禁用或删除它们。

      如何列出表上的所有触发器

      SELECT trigger_name, event_manipulation, actiontiming
FROM information_schema.triggers
WHERE event_object_table = 'products';

      如何临时禁用触发器

      -- 禁用特定的触发器
ALTER TABLE products DISABLE TRIGGER trigger_product_audit;

-- 禁用表上的所有触发器
ALTER TABLE products DISABLE TRIGGER ALL;

      在进行批量数据迁移时，这种功能非常有用——出于性能考虑，您可以选择跳过触发器的执行。

      如何重新启用触发器

      ALTER TABLE products ENABLE TRIGGER trigger_product_audit;

      如何删除触发器

      DROP TRIGGER IF EXISTS trigger_product_audit ON products;

      需要注意的是，删除触发器并不会同时删除与之关联的功能。如果不再需要这些功能，还需要单独进行删除操作：

      DROP FUNCTION IF EXISTS log_product_changes();

      何时使用触发器

      触发器在某些特定场景中能发挥很好的作用。以下是适合使用触发器的情形：

      • 审计记录功能：能够自动记录谁在什么时间修改了哪些数据，正如本教程前面所介绍的那样。

      • 衍生数据的维护：确保计算列、计数器或汇总表与源数据保持同步。

      • 数据验证：执行那些CHECK约束无法实现的业务规则，例如跨表的数据验证。

      • 自动添加时间戳：无需依赖应用程序层，即可自动设置created_at和updated_at字段的值。

      何时应避免使用触发器

      触发器是非常强大的工具，但它们也伴随着一些弊端。在以下情况下，使用触发器之前你应该慎重考虑：

      • 复杂的业务逻辑：如果某段逻辑涉及调用外部API、发送电子邮件或协调多步骤的工作流程，那么它应该属于应用程序层。触发器应当保持简洁性。

      • 对性能要求较高的批量操作：对于那些经常需要进行大量插入或更新操作的表格来说，行级触发器会带来严重的性能开销。如果你需要插入数百万条记录，那么这些触发器将会被执行数百万次。

      • 级联触发器

        ：当一个触发器的动作触发了另一个触发器，而那个触发器又引发了另一个触发器时，调试工作会变得极其困难。如果你发现自己正在构建这样的触发器链，请重新审视你的设计。

      • 开发者需要能够轻松理解的业务逻辑：有时人们会将触发器称为“隐藏的逻辑”，因为它们会在不显式出现在应用程序代码中的情况下自动执行。如果你的团队经常询问“为什么这个字段会发生变化？”，而答案总是“是因为有某个触发器在起作用”，那么这说明，如果将这些逻辑放在应用程序层或通过显式调用的存储过程中，可能会更容易被理解。

      一个实用的判断标准是：如果某段逻辑与数据紧密关联，并且无论哪个客户端或服务访问该表格，这段逻辑都应当始终被执行，那么使用触发器是合适的。而如果这段逻辑依赖于应用程序的具体上下文（比如当前用户的会话信息、功能开关或外部状态），那么它就应该属于应用程序层。

      结论

      通过本教程，你了解了什么是数据库触发器以及它们在PostgreSQL中的工作原理。你还亲手创建了三个实用的触发器：一个自动更新时间戳的触发器、一个完整的审计日志系统，以及一个用于数据验证的触发器。同时，你也学会了BEFORE和AFTER触发器、行级触发器和语句级触发器之间的区别，以及何时可以使用NEW和OLD变量。

      触发器是确保数据一致性以及在数据库层面执行业务规则的重要工具。请将它们用于那些以数据为中心的操作中，并尽量保持相关逻辑的简洁性。

      如果你觉得本教程对你有帮助，可以通过LinkedIn和X与我联系。

Tags:
Comments are closed.