PostgreSQL 触发器中 NEW 和 OLD 的高效使用：避坑指南（新手篇）

PostgreSQL 触发器中 NEW 和 OLD 的高效使用：避坑指南（新手篇）

大家好，我是你们的 PostgreSQL 小助手“波斯猫”！今天咱们来聊聊 PostgreSQL 触发器中两个非常重要的变量：NEW 和 OLD。对于刚接触触发器的开发者来说，这两个家伙可能会让你又爱又恨。别担心，波斯猫这就带你深入了解它们，并教你如何高效使用，避免踩坑！

什么是触发器？

在深入了解 NEW 和 OLD 之前，我们先简单回顾一下触发器。触发器，顾名思义，就是由特定事件“触发”执行的特殊存储过程。这些事件可以是 INSERT、UPDATE 或 DELETE 操作。你可以把触发器想象成一个“监控器”，它时刻监视着数据库表的动静，一旦发生指定的操作，就会自动执行预先定义好的代码。

认识 NEW 和 OLD

NEW 和 OLD 是触发器函数内部可以访问的特殊变量，它们代表着触发事件发生前后的数据行。具体来说：

• NEW： 代表 INSERT 或 UPDATE 操作后新插入或更新的数据行。在 DELETE 操作中，NEW 为 NULL。
• OLD： 代表 UPDATE 或 DELETE 操作前被更新或删除的数据行。在 INSERT 操作中，OLD 为 NULL。

这两个变量的类型与触发器所关联的表的行类型相同。你可以像访问普通记录一样访问 NEW 和 OLD 中的字段，例如 NEW.column_name 或 OLD.column_name。

触发器类型与 NEW/OLD 的关系

PostgreSQL 支持两种类型的触发器：

1. 行级触发器 (FOR EACH ROW)： 对表的每一行数据变化都会触发一次。在这种触发器中，NEW 和 OLD 非常有用，可以让你访问到每一行变化前后的具体数据。
2. 语句级触发器 (FOR EACH STATEMENT)： 整个语句执行一次触发器。这种触发器中不能使用NEW和OLD。

下表总结了不同操作和触发器类型下 NEW 和 OLD 的值：

实战演练：NEW 和 OLD 的应用

光说不练假把式，咱们来通过几个具体的例子，看看 NEW 和 OLD 在实际开发中如何应用。

1. 审计日志记录

假设我们有一个 products 表，用于存储产品信息。我们希望记录每次产品信息的修改，包括修改时间、修改人和修改前后的值。

首先，创建 products 表和审计日志表 products_audit：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2)
);
 
CREATE TABLE products_audit (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    old_name VARCHAR(255),
    old_price NUMERIC(10, 2),
    new_name VARCHAR(255),
    new_price NUMERIC(10, 2),
    updated_by VARCHAR(255) NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc')
);

然后，创建一个触发器函数 log_product_changes：

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录更新操作
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO products_audit (product_id, old_name, old_price, new_name, new_price, updated_by)
        VALUES (OLD.id, OLD.name, OLD.price, NEW.name, NEW.price, current_user);
        RETURN NEW; -- 对于 AFTER 触发器，返回值会被忽略
    END IF;
    RETURN NULL; -- 对于其他操作，不做任何事
END;
$$ LANGUAGE plpgsql;

最后，创建触发器 product_update_trigger，并将其与 products 表的 UPDATE 操作关联：

CREATE TRIGGER product_update_trigger
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

现在，每次更新 products 表中的数据时，都会在 products_audit 表中插入一条记录，详细记录修改前后的信息。

2. 自动更新时间戳

我们希望在每次更新 products 表的行时，自动更新一个名为 updated_at 的时间戳字段。

首先，给 products 表添加 updated_at 字段：

ALTER TABLE products ADD COLUMN updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc');

然后，创建一个触发器函数 update_product_timestamp：

CREATE OR REPLACE FUNCTION update_product_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW() AT TIME ZONE 'utc';
    RETURN NEW; -- 对于 BEFORE 触发器，返回值是修改后的行
END;
$$ LANGUAGE plpgsql;

最后，创建触发器 product_update_timestamp_trigger，并将其与 products 表的 UPDATE 操作关联：

CREATE TRIGGER product_update_timestamp_trigger
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_product_timestamp();

在这个例子中，我们使用了 BEFORE UPDATE 触发器。在 BEFORE 触发器中，我们可以修改 NEW 变量的值，这些修改会反映到实际插入或更新的数据行中。通过将 NEW.updated_at 设置为当前时间，我们实现了自动更新时间戳的功能。

3. 防止删除关键数据

假设我们有一个 users 表，存储用户信息。我们希望防止误删除管理员用户。

首先，创建 users 表：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE
);

然后，创建一个触发器函数 prevent_admin_deletion：

CREATE OR REPLACE FUNCTION prevent_admin_deletion()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_admin = TRUE THEN
        RAISE EXCEPTION 'Cannot delete admin user: %', OLD.username;
    END IF;
    RETURN OLD; -- 对于 BEFORE 触发器，返回值是即将被删除的行
END;
$$ LANGUAGE plpgsql;

最后，创建触发器 user_delete_trigger，并将其与 users 表的 DELETE 操作关联：

CREATE TRIGGER user_delete_trigger
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION prevent_admin_deletion();

在这个例子中，我们使用了 BEFORE DELETE 触发器。在 BEFORE 触发器中，我们可以通过 RAISE EXCEPTION 语句抛出异常，阻止触发事件的发生。如果 OLD.is_admin 为 TRUE，则抛出异常，阻止删除操作。

常见错误及避免方法

1. 混淆 NEW 和 OLD 的适用场景： 一定要记住，NEW 只在 INSERT 和 UPDATE 中有效，OLD 只在 UPDATE 和 DELETE 中有效。在错误的场景中使用会导致错误。
2. 在语句级触发器中使用NEW和OLD： 语句级触发器不能使用这两个变量。
3. 忘记 RETURN 语句： 在 BEFORE 触发器中，一定要返回 NEW（对于 INSERT 和 UPDATE）或 OLD（对于 DELETE），否则会导致数据插入、更新或删除失败。在 AFTER 触发器中，返回值通常会被忽略，但为了代码清晰，建议仍然返回 NEW 或 OLD。
4. 过度使用触发器： 触发器虽然强大，但过度使用会导致性能问题和维护困难。尽量将业务逻辑放在应用程序中，只在必要时使用触发器。
5. 触发器中的死循环: 在UPDATE触发器中，再次更新同一张表, 可能导致死循环。在设计的时候要小心。

总结

NEW 和 OLD 是 PostgreSQL 触发器中非常强大的工具，可以让你轻松访问触发事件发生前后的数据行。通过合理使用这两个变量，你可以实现各种复杂的业务逻辑，例如审计日志记录、自动更新时间戳、数据校验等。但同时，也要注意避免常见错误，确保触发器的正确性和高效性。希望这篇“避坑指南”能帮助你更好地掌握 PostgreSQL 触发器，写出更健壮、更高效的代码！如果你还有其他问题，欢迎随时向波斯猫提问哦！

附：触发器调试技巧

调试触发器可能比较麻烦，这里分享几个小技巧：

1. 使用 RAISE NOTICE 打印信息： 可以在触发器函数中使用 RAISE NOTICE 语句打印 NEW、OLD 或其他变量的值，方便调试。

   CREATE OR REPLACE FUNCTION my_trigger_function()
   RETURNS TRIGGER AS $$
   BEGIN
       RAISE NOTICE 'NEW.id: %, OLD.id: %', NEW.id, OLD.id;
       -- 其他逻辑
   END;
   $$ LANGUAGE plpgsql;

2. 使用 psql 客户端的 \echo 命令： 可以在 psql 客户端中设置 \echo 选项，显示执行的 SQL 语句，方便跟踪触发器的执行过程。

   psql -U your_user -d your_database
   \set ECHO all

3. 简化问题: 触发器逻辑比较复杂时，可以先创建一个简单的测试表和测试数据，逐步添加触发器逻辑，边添加边测试，确保每一步都正确。

希望这些技巧能帮助到大家!