PostgreSQL触发器进阶：WHEN子句精细化控制实战

PostgreSQL触发器进阶：WHEN子句精细化控制实战

你好！在PostgreSQL的日常开发中，触发器（Trigger）是咱们实现复杂业务逻辑、保证数据一致性的得力助手。相信你对触发器的基本概念已经有所了解，但你是否想过，如何让触发器更“聪明”，只在特定条件下才执行？今天，咱们就来聊聊触发器中的WHEN子句，一起探索如何通过它实现更精细化的控制。

1. 为什么需要WHEN子句？

在没有WHEN子句的情况下，触发器会在每次关联事件（INSERT、UPDATE、DELETE、TRUNCATE）发生时都被触发。但实际应用中，很多时候咱们只关心满足特定条件的数据变更。例如：

• 订单管理系统：只有当订单状态从“待支付”变为“已支付”时，才触发更新库存的操作。
• 日志记录系统：只记录用户对特定敏感字段（如密码、余额）的修改。
• 数据同步系统：仅当数据满足特定业务规则时，才触发同步到其他数据库的操作。

如果每次都无差别地触发，不仅会增加数据库的负担，还可能导致不必要的逻辑执行，甚至引发错误。这时，WHEN子句就派上用场了！

2. WHEN子句的基本语法

WHEN子句是CREATE TRIGGER语句的一部分，用于指定触发器执行的条件。其基本语法如下：

CREATE [ OR REPLACE ] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } trigger_function ( arguments );

• trigger_name: 触发器名称。
• BEFORE | AFTER | INSTEAD OF: 触发时机。BEFORE表示在事件发生前触发，AFTER表示在事件发生后触发，INSTEAD OF用于视图上的触发器。
• event: 触发事件，可以是INSERT、UPDATE、DELETE或TRUNCATE。
• table_name: 触发器关联的表名。
• FOR [ EACH ] { ROW | STATEMENT }: 指定触发器的类型。ROW表示行级触发器（对每一行受影响的行都会触发），STATEMENT表示语句级触发器（对整个语句只触发一次）。
• WHEN ( condition ): 触发条件，只有当condition为真时，触发器函数才会被执行。condition是一个布尔表达式，可以引用NEW和OLD伪记录（仅在行级触发器中可用）。
• EXECUTE { FUNCTION | PROCEDURE } trigger_function ( arguments ): 指定要执行的触发器函数及其参数。 PostgreSQL 11及以后版本可以使用PROCEDURE，之前的版本使用FUNCTION。

3. WHEN子句中的NEW和OLD

在行级触发器中，WHEN子句可以访问两个特殊的伪记录：NEW和OLD。它们分别代表：

• NEW: 对于INSERT和UPDATE事件，NEW表示将要插入或更新的新行数据。对于DELETE事件，NEW为NULL。
• OLD: 对于UPDATE和DELETE事件，OLD表示更新前或将要删除的旧行数据。对于INSERT事件，OLD为NULL。

通过NEW和OLD，咱们可以在WHEN子句中访问到数据变更前后的值，从而实现更复杂的条件判断。

4. 实战案例

4.1 案例1：订单状态变更触发库存更新

假设咱们有一个订单表orders，包含order_id、status（订单状态）和product_id等字段。咱们希望当订单状态从“待支付”变为“已支付”时，自动更新对应产品的库存。

首先，创建订单表和产品库存表：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    stock INT NOT NULL
);
 
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    status VARCHAR(20) NOT NULL DEFAULT '待支付'
);
 
INSERT INTO products (product_id, stock) VALUES (1, 100), (2, 50);

然后，创建一个触发器函数，用于更新库存：

CREATE OR REPLACE FUNCTION update_stock() RETURNS TRIGGER AS $$
BEGIN
    UPDATE products
    SET stock = stock - 1
    WHERE product_id = NEW.product_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

最后，创建触发器，并使用WHEN子句指定触发条件：

CREATE TRIGGER order_status_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status = '待支付' AND NEW.status = '已支付')
EXECUTE FUNCTION update_stock();

现在，当咱们更新订单状态时：

-- 触发器会被触发
UPDATE orders SET status = '已支付' WHERE order_id = 1;
 
-- 触发器不会被触发
UPDATE orders SET status = '已发货' WHERE order_id = 2;

只有当订单状态从“待支付”变为“已支付”时，update_stock函数才会被执行，从而更新库存。

4.2 案例2：记录敏感字段修改

假设有一个用户表users，包含user_id、username和password等字段。咱们希望记录用户对password字段的修改。

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);
 
CREATE TABLE user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    old_password VARCHAR(100),
    new_password VARCHAR(100),
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器函数：

CREATE OR REPLACE FUNCTION log_password_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (user_id, old_password, new_password)
    VALUES (OLD.user_id, OLD.password, NEW.password);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器：

CREATE TRIGGER password_change_trigger
AFTER UPDATE ON users
FOR EACH ROW
WHEN (OLD.password IS DISTINCT FROM NEW.password)
EXECUTE FUNCTION log_password_change();

这里使用了IS DISTINCT FROM，因为即使新旧密码在逻辑上相同（例如，都是空字符串或NULL），咱们也希望记录下来。

4.3 案例3：条件数据同步

假设需要将orders表中满足特定条件的数据同步到另一个数据库的orders_backup表。这个特定条件是product_id大于1的订单。

-- 假设已经连接到另一个数据库，并创建了orders_backup表
CREATE OR REPLACE FUNCTION sync_orders() RETURNS TRIGGER AS $$
BEGIN
    -- 假设使用dblink或其他方式进行跨数据库操作
    PERFORM dblink_connect('other_db'); -- 假设的dblink连接函数
    EXECUTE 'INSERT INTO orders_backup (order_id, product_id, status) VALUES ($1, $2, $3)'
    USING NEW.order_id, NEW.product_id, NEW.status;
      PERFORM dblink_disconnect('other_db');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER orders_sync_trigger
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.product_id > 1)
EXECUTE FUNCTION sync_orders();

这样，只有当新插入的订单的product_id大于1时，才会触发同步操作。

5. WHEN子句的注意事项

• 性能考虑：虽然WHEN子句可以减少不必要的触发器函数执行，但它本身也会带来一定的性能开销。数据库需要对每一行数据评估WHEN条件。因此，应尽量保持WHEN条件的简单高效。
• 条件逻辑：WHEN子句中的条件应该尽可能清晰明确，避免复杂的逻辑判断。复杂的逻辑应该放在触发器函数中处理。
• NEW和OLD的使用：只有行级触发器才能使用NEW和OLD。语句级触发器不能使用。
• 触发器级联：如果一个触发器修改了另一个触发器关联的表，可能会导致触发器级联。在使用WHEN子句时，要特别注意避免无限循环触发。
• 与约束的配合: WHEN 条件不能违反表上已定义的约束。例如，如果有一个 UNIQUE 约束，WHEN 条件不能尝试创建一个违反该约束的重复行。
• 测试: 充分测试带有 WHEN 子句的触发器，确保它们在所有预期场景下都能正确工作。尤其要测试边界条件和异常情况。

6. 总结

WHEN子句是PostgreSQL触发器中一个非常实用的特性，它允许咱们根据具体条件来控制触发器的执行，从而实现更精细化的数据库操作。通过合理使用WHEN子句，咱们可以提高数据库的性能，避免不必要的逻辑执行，使触发器更加“智能”。

希望通过今天的分享，你对PostgreSQL触发器中的WHEN子句有了更深入的理解。在实际开发中，不妨多尝试使用WHEN子句，让你的数据库操作更加高效、精准！ 如果你在使用过程中遇到任何问题，或者有更好的实践经验，欢迎随时交流分享！