WEBKT

PostgreSQL 触发器深度实践：玩转 INSERT、UPDATE 和 DELETE

276 0 0 0

大家好，我是你们的“数据库老司机”！今天咱们来聊聊 PostgreSQL 里一个非常强大的功能——触发器（Trigger）。这玩意儿就像数据库里的“哨兵”，能自动监控数据库的各种操作，并在特定事件发生时执行预定义的动作。是不是听起来就很酷？

什么是触发器？

触发器，顾名思义，就是由某个事件“触发”执行的一段代码。在 PostgreSQL 中，这些事件通常是 INSERT、UPDATE 或 DELETE 操作。你可以把触发器想象成一个“事件监听器”，当它“听到”感兴趣的事件时，就会自动执行你预先设定好的“反应”——也就是触发器函数。

为什么需要触发器？

你可能会问，我直接在应用程序里处理这些逻辑不行吗？干嘛非得用触发器？

当然可以！但触发器有它独特的优势：

  • 数据一致性： 触发器在数据库层面保证数据的一致性。无论你是通过应用程序、命令行还是其他工具操作数据，触发器都会被执行，确保数据始终符合你的预期。
  • 业务逻辑集中： 将一些通用的业务逻辑放在触发器中，可以避免在多个应用程序中重复编写相同的代码，提高代码的可维护性。
  • 审计跟踪： 触发器可以记录数据的变更历史，方便你追踪数据的来源和变化过程。
  • 自动化任务： 触发器可以自动执行一些任务，例如数据备份、数据同步等。

触发器函数的编写

触发器函数是触发器的核心。它定义了触发器被触发时要执行的具体操作。PostgreSQL 支持多种语言编写触发器函数，例如 PL/pgSQL、PL/Python、PL/Perl 等。其中，PL/pgSQL 是最常用的语言。

一个简单的例子

假设我们有一个 products 表，记录了产品的信息，包括 id、name 和 price。现在，我们想创建一个触发器，当产品的价格发生变化时，自动记录变更历史到 product_price_logs 表。

首先，创建 product_price_logs 表：

CREATE TABLE product_price_logs (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    old_price NUMERIC,
    new_price NUMERIC,
    changed_at TIMESTAMP DEFAULT now()
);

然后，编写触发器函数：

CREATE OR REPLACE FUNCTION log_product_price_change()
RETURNS TRIGGER AS $$
BEGIN
    -- OLD 表示更新前的行数据
    -- NEW 表示更新后的行数据
    IF NEW.price <> OLD.price THEN
        INSERT INTO product_price_logs(product_id, old_price, new_price)
        VALUES(OLD.id, OLD.price, NEW.price);
    END IF;

    -- 返回 NEW，表示继续执行后续操作
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

最后，创建触发器：

CREATE TRIGGER product_price_change_trigger
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_price_change();

现在，当你更新 products 表中的 price 字段时，触发器会自动记录变更历史到 product_price_logs 表。

触发器函数的关键点

  • RETURNS TRIGGER： 触发器函数必须返回 TRIGGER 类型。
  • OLD 和 NEW： 这两个特殊的变量分别表示操作前后的行数据。对于 INSERT 操作，OLD 为 NULL；对于 DELETE 操作，NEW 为 NULL；对于 UPDATE 操作，OLD 和 NEW 都可用。
  • RETURN 语句： 触发器函数的 RETURN 语句决定了触发器的行为。
    • RETURN NEW：表示继续执行后续操作（例如 INSERT、UPDATE 或 DELETE）。
    • RETURN OLD：对于UPDATE和DELETE操作，返回OLD会继续执行操作，但是INSERT返回OLD相当于RETURN NULL;
    • RETURN NULL：表示取消后续操作。这通常用于 BEFORE 触发器，可以阻止不符合条件的 INSERT、UPDATE 或 DELETE 操作。
  • TG_OP: 这个变量可以获取到当前触发器是被什么操作触发的, INSERT, UPDATE or DELETE.

不同操作类型的处理

INSERT 操作

对于 INSERT 操作，触发器函数中只有 NEW 变量可用，OLD 变量为 NULL。你可以在 BEFORE INSERT 触发器中修改 NEW 变量的值，从而改变插入的数据。

例如，我们可以在插入产品时自动生成一个唯一的 SKU：

CREATE OR REPLACE FUNCTION generate_sku()
RETURNS TRIGGER AS $$
BEGIN
    NEW.sku := 'SKU-' || nextval('product_sku_seq');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER generate_sku_trigger
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION generate_sku();

UPDATE 操作

对于 UPDATE 操作，OLD 和 NEW 变量都可用。你可以在 BEFORE UPDATE 触发器中修改 NEW 变量的值，从而改变更新后的数据。你也可以通过比较 OLD 和 NEW 变量的值，判断哪些字段发生了变化。

例如，我们可以限制用户不能将产品的价格降低到 0 以下：

CREATE OR REPLACE FUNCTION check_price_update()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_price_update_trigger
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION check_price_update();

DELETE 操作

对于 DELETE 操作，触发器函数中只有 OLD 变量可用，NEW 变量为 NULL。你可以在 BEFORE DELETE 触发器中阻止删除操作，或者在 AFTER DELETE 触发器中记录删除的数据。

例如，我们可以阻止用户删除某些特定的产品：

CREATE OR REPLACE FUNCTION prevent_product_deletion()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.id IN (1, 2, 3) THEN
        RAISE EXCEPTION 'Cannot delete products with ID 1, 2, or 3';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_product_deletion_trigger
BEFORE DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_product_deletion();

触发器的执行顺序

一个表上可以创建多个触发器。当多个触发器被同一个事件触发时，它们的执行顺序取决于触发器的名称。PostgreSQL 会按照触发器名称的字母顺序执行触发器。

如果你需要控制触发器的执行顺序，可以通过修改触发器的名称来实现。例如，你可以将触发器命名为 01_trigger_a、02_trigger_b、03_trigger_c，这样就可以确保 trigger_a 在 trigger_b 之前执行，trigger_b 在 trigger_c 之前执行。

触发器的常见问题及优化

循环触发

如果触发器函数中又执行了触发该触发器的操作，就会导致循环触发。例如，一个 UPDATE 触发器又更新了同一张表的数据，这可能会导致触发器无限循环执行。

为了避免循环触发，你需要在触发器函数中仔细检查 OLD 和 NEW 变量的值，确保不会重复执行相同的操作。或者，你可以在触发器函数中使用一些标志变量，防止重复执行。

性能问题

触发器虽然强大，但也会带来一定的性能开销。因为每次触发事件时，都需要执行触发器函数。如果触发器函数过于复杂，或者触发器被频繁触发，就会影响数据库的性能。

为了优化触发器的性能，你需要：

  • 尽量简化触发器函数： 触发器函数应该只包含必要的逻辑，避免执行复杂的计算或查询。
  • 避免不必要的触发： 只在需要的时候创建触发器，避免创建不必要的触发器。
  • 批量操作： 如果你需要对大量数据进行操作，尽量使用批量操作，而不是逐行操作。因为批量操作只会触发一次触发器，而逐行操作会触发多次触发器。
  • 延迟触发器： 如果你不需要立即执行触发器函数，可以将触发器设置为延迟触发。延迟触发器会在事务提交时执行，而不是在每次操作时执行。这可以减少触发器的执行次数，提高性能。
  • 条件触发器： 你可以使用 WHEN 子句创建条件触发器。条件触发器只有在满足特定条件时才会被触发。这可以减少不必要的触发器执行，提高性能。

触发器和约束的配合

触发器经常和约束（Constraints）一起使用，以实现更复杂的数据验证和完整性规则。约束用于限制表中数据的取值范围，例如 UNIQUE 约束、CHECK 约束、FOREIGN KEY 约束等。触发器可以用于实现更复杂的约束，例如跨表约束、动态约束等。

例如，我们可以使用触发器和约束来实现一个订单系统，确保订单的总金额不能超过用户的信用额度：

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    credit_limit NUMERIC NOT NULL
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount NUMERIC NOT NULL
);

-- 触发器函数
CREATE OR REPLACE FUNCTION check_order_total()
RETURNS TRIGGER AS $$
DECLARE
    user_credit_limit NUMERIC;
BEGIN
    -- 获取用户的信用额度
    SELECT credit_limit INTO user_credit_limit FROM users WHERE id = NEW.user_id;

    -- 检查订单总金额是否超过信用额度
    IF NEW.total_amount > user_credit_limit THEN
        RAISE EXCEPTION 'Order total amount exceeds credit limit';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 触发器
CREATE TRIGGER check_order_total_trigger
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION check_order_total();

在这个例子中，我们使用 FOREIGN KEY 约束确保订单表中的 user_id 必须存在于用户表中。同时，我们使用触发器确保订单的总金额不能超过用户的信用额度。这样，我们就实现了跨表的数据完整性约束。

总结

触发器是 PostgreSQL 中一个非常强大的功能，可以帮助你实现数据一致性、业务逻辑集中、审计跟踪和自动化任务。但是，触发器也会带来一定的性能开销，你需要谨慎使用。

希望这篇文章能帮助你更好地理解 PostgreSQL 触发器。如果你有任何问题，欢迎随时提问！咱们下期再见！

数据库老司机 PostgreSQL触发器数据库

评论点评