PostgreSQL 触发器实战：数据同步与日志记录，就这么简单！

PostgreSQL 触发器实战：数据同步与日志记录，就这么简单！

大家好，我是你们的硬核老朋友，码农老王！今天咱们来聊聊 PostgreSQL 里一个非常强大的功能——触发器（Trigger）。这玩意儿，用好了，能帮你自动完成很多数据库操作，比如数据同步、日志记录，简直不要太方便！

很多刚接触 PostgreSQL 的小伙伴，可能觉得触发器挺神秘的，感觉很高大上。其实啊，触发器就是一种特殊的存储过程，它会在你对数据库进行特定操作（比如 INSERT、UPDATE、DELETE）的时候，自动“触发”执行。

触发器：到底是个啥？

咱们先来个官方解释（虽然有点枯燥，但很重要）：触发器是一种与表相关联的数据库对象，当对表执行特定类型的 SQL 语句时，会自动激活并执行预定义的操作。

说人话就是：你给表设置一个“机关”，当你对表做某些动作的时候，“机关”就会被触动，然后执行一系列你事先设定好的操作。

触发器：能干啥？

触发器的用处可大了去了，这里老王给你列举几个最常见的：

1. 数据同步： 比如你有两个表，一个是订单表，一个是库存表。你希望每次插入新的订单，库存表里的对应商品数量就自动减少。用触发器，轻松搞定！
2. 日志记录： 记录用户对数据库的各种操作，比如谁在什么时间修改了哪条数据。这对于审计和问题追踪非常重要。
3. 数据校验： 在数据插入或更新之前，进行一些校验，确保数据的合法性。比如，你规定用户年龄不能小于 18 岁，就可以用触发器来检查。
4. 强制约束： 实现一些比 CHECK 约束更复杂的业务规则。
5. 自动计算: 比如你有一个商品价格表,每次更新价格后,自动更新平均价格。

触发器：怎么用？

接下来，咱们就来实战演练一下，看看触发器到底怎么用。老王会用几个具体的例子，手把手教你。

场景一：数据同步（订单表与库存表）

假设我们有两个表：

• orders (订单表)：order_id (订单ID), product_id (商品ID), quantity (数量)
• inventory (库存表)：product_id (商品ID), stock (库存数量)

我们希望实现：每次向 orders 表插入一条新订单，inventory 表中对应商品的库存数量就自动减少。

1. 创建表结构

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL
);
 
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock INT NOT NULL
);
 
-- 初始化库存数据
INSERT INTO inventory (product_id, stock) VALUES (1, 100);
INSERT INTO inventory (product_id, stock) VALUES (2, 50);

2. 创建触发器函数

触发器函数是触发器真正执行的操作。它是一个普通的 PostgreSQL 函数，可以用 PL/pgSQL、PL/Python 等语言编写。

CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $$
BEGIN
    -- 减少库存
    UPDATE inventory
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
 
    -- 如果库存不足，可以抛出异常
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % not found in inventory', NEW.product_id;
    END IF;
    
      IF (SELECT stock FROM inventory WHERE product_id = NEW.product_id) < 0 THEN
          RAISE EXCEPTION 'Insufficient stock for product %', NEW.product_id;
      END IF;
 
    RETURN NEW; -- 对于 AFTER 触发器，通常返回 NEW
END;
$$ LANGUAGE plpgsql;

代码解释：

• CREATE OR REPLACE FUNCTION update_inventory(): 创建或替换一个名为 update_inventory 的函数。
• RETURNS TRIGGER: 指定这个函数是一个触发器函数。
• AS $$ ... $$ LANGUAGE plpgsql: 函数体，用 PL/pgSQL 语言编写。
• NEW: 这是一个特殊的变量，代表触发器事件发生时的新数据行（对于 INSERT 和 UPDATE 操作）。
• UPDATE inventory ...: 更新 inventory 表的 stock 字段。
• WHERE product_id = NEW.product_id: 更新条件，只更新与新订单相关的商品。
• IF NOT FOUND THEN ...: 如果没有找到对应的商品，抛出异常。
• IF (SELECT stock...<0) THEN ...:如果更新后库存小于0,抛出异常.
• RETURN NEW: 对于 AFTER 触发器，通常返回 NEW。

3. 创建触发器

CREATE TRIGGER update_inventory_trigger
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_inventory();

代码解释：

• CREATE TRIGGER update_inventory_trigger: 创建一个名为 update_inventory_trigger 的触发器。
• AFTER INSERT ON orders: 指定触发器在 orders 表的 INSERT 操作之后触发。
• FOR EACH ROW: 指定触发器为行级触发器，即对每一行受影响的数据都会触发一次。
• EXECUTE FUNCTION update_inventory(): 指定触发器触发时执行的函数。

4. 测试

-- 插入一条新订单
INSERT INTO orders (product_id, quantity) VALUES (1, 10);
 
-- 查看库存
SELECT * FROM inventory;
 
-- 再次插入一条新订单,测试库存不足的情况
INSERT INTO orders (product_id, quantity) VALUES (1, 100);

你会发现，inventory 表中 product_id 为 1 的商品的库存数量已经自动减少了 10。第二次插入因为会导致库存小于0,所以会触发异常。

场景二：日志记录（记录用户操作）

假设我们有一个 users 表：

• users (用户表)：user_id (用户ID), username (用户名), email (邮箱)

我们希望记录用户对 users 表的修改操作，包括修改时间、修改人、修改前后的数据。

1. 创建表结构

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255)
);
 
CREATE TABLE user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    old_username VARCHAR(255),
    new_username VARCHAR(255),
    old_email VARCHAR(255),
    new_email VARCHAR(255),
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_by VARCHAR(255) DEFAULT CURRENT_USER
);

2. 创建触发器函数

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入日志记录
    INSERT INTO user_logs (user_id, old_username, new_username, old_email, new_email)
    VALUES (OLD.user_id, OLD.username, NEW.username, OLD.email, NEW.email);
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

代码解释
OLD: 这个变量代表的是更新前的数据。

3. 创建触发器

CREATE TRIGGER log_user_changes_trigger
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

4. 测试

-- 插入一条用户数据
INSERT INTO users (username, email) VALUES ('zhangsan', 'zhangsan@example.com');
 
-- 修改用户信息
UPDATE users SET username = 'lisi', email = 'lisi@example.com' WHERE user_id = 1;
 
-- 查看日志
SELECT * FROM user_logs;

你会发现，user_logs 表中已经记录了这次修改操作的详细信息。

触发器：进阶技巧

1. 触发器的类型：
   • BEFORE: 在触发事件发生之前执行。
   • AFTER: 在触发事件发生之后执行。
   • INSTEAD OF: 替换触发事件，而不是在之前或之后执行。INSTEAD OF 触发器只能用于视图，不能用于表。
2. 触发器的级别：
   • FOR EACH ROW: 行级触发器，对每一行受影响的数据都会触发一次。
   • FOR EACH STATEMENT: 语句级触发器，对整个 SQL 语句只触发一次。
3. 触发器的条件：
   • 可以在创建触发器时使用 WHEN 子句指定触发条件，只有满足条件时才会触发。
4. 触发器中的特殊变量
   • TG_OP：触发器操作的类型（INSERT、UPDATE、DELETE 或 TRUNCATE）。
   • TG_TABLE_NAME：触发器所在的表的名称。
   • TG_WHEN: 触发器触发的时间（BEFORE,AFTER,INSTEAD OF）。
   • TG_LEVEL: 触发器的级别(ROW,STATEMENT)。
   • TG_NARGS: 传递给触发器函数的参数数量。
   • TG_ARGV: 传递给触发器函数的参数数组。
5. 禁用/启用触发器

--禁用
ALTER TABLE table_name DISABLE TRIGGER trigger_name;
--启用
ALTER TABLE table_name ENABLE TRIGGER trigger_name;
--禁用所有
ALTER TABLE table_name DISABLE TRIGGER ALL;
--启用所有
ALTER TABLE table_name ENABLE TRIGGER ALL;

触发器：注意事项

• 触发器虽然强大，但也要谨慎使用。过多的触发器可能会导致性能问题，因为每次触发事件都会执行额外的操作。
• 触发器中的逻辑应该尽量简单，避免复杂的计算和耗时的操作。
• 触发器可能会导致连锁反应，比如一个触发器触发了另一个触发器，另一个触发器又触发了第三个触发器…… 这可能会导致死循环或不可预料的结果。所以在设计时要考虑清楚。
• 触发器的错误可能比较隐蔽，难以调试，要仔细测试。

总结

好啦，今天关于 PostgreSQL 触发器的内容就讲到这里。希望老王的讲解能让你对触发器有一个更清晰的认识。记住，触发器是个好东西，但要用对地方，用好方法。如果你在实际项目中遇到了触发器相关的问题，欢迎随时来找老王交流！

下次再见，拜拜！