WEBKT

PostgreSQL 触发器实战：数据审计、同步、校验，业务规则轻松搞定！

42 0 0 0

PostgreSQL 触发器实战：数据审计、同步、校验，业务规则轻松搞定！

什么是触发器？

触发器的类型

触发器的语法

触发器的实际应用场景

1. 数据审计

2. 数据同步

3. 数据校验

4. 自动生成序列号

5. 实现复杂的业务规则

触发器的注意事项

总结

PostgreSQL 触发器实战：数据审计、同步、校验，业务规则轻松搞定！

嘿，老铁们，大家好！我是你们的数据库老司机，今天咱们来聊聊 PostgreSQL 数据库里的一个利器——触发器（Trigger）。这玩意儿就像数据库里的“变形金刚”，能根据你设定的规则，在数据发生变化的时候，自动执行一些操作。听起来是不是很酷？接下来，我将用通俗易懂的语言，结合实际案例，带你深入了解触发器在实际业务场景中的应用，让你也能轻松驾驭这个“变形金刚”！

什么是触发器？

简单来说，触发器就是一种特殊的存储过程，它依附于特定的表，并在特定的事件（比如 INSERT、UPDATE、DELETE）发生时被自动触发执行。你可以把它理解成一个“事件监听器”，一旦监听到预设的事件发生，就会自动执行你预先编写好的代码。

触发器的类型

PostgreSQL 提供了多种类型的触发器，以满足不同的业务需求。主要分为以下几类：

  • 行级触发器（Row-Level Trigger）：在每一行数据发生变化时触发。这意味着，如果你的 UPDATE 语句修改了 10 行数据，那么行级触发器就会被触发 10 次。
  • 语句级触发器（Statement-Level Trigger）：在整个语句执行完毕后触发，无论语句影响了多少行数据。例如，一个 UPDATE 语句修改了 10 行数据，语句级触发器只会触发 1 次。
  • BEFORE 触发器：在事件发生之前触发，可以用于修改数据或者阻止事件的发生（例如，数据校验失败时阻止 INSERT 操作）。
  • AFTER 触发器：在事件发生之后触发，可以用于数据审计、数据同步等操作。
  • INSTEAD OF 触发器：它用于替代 INSERT、UPDATE 或 DELETE 操作。这在处理视图（View）时非常有用，因为视图本身并不存储数据，而是基于其他表的数据生成的。

触发器的语法

了解了触发器的类型，咱们再来看看它的基本语法结构：

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }  -- 触发事件：INSERT, UPDATE, DELETE
ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]  -- 行级还是语句级触发器
[ WHEN ( condition ) ]  -- 触发条件
EXECUTE PROCEDURE function_name ( arguments );  -- 执行的函数

解释一下：

  • trigger_name：触发器的名称，在数据库中必须唯一。
  • BEFORE | AFTER | INSTEAD OF：触发器的触发时机。
  • event：触发事件，可以是 INSERT、UPDATE、DELETE。OR 用于定义多个触发事件。
  • table_name：触发器关联的表名。
  • FOR [ EACH ] { ROW | STATEMENT }：定义触发器是行级还是语句级。省略 FOR EACH 等同于 STATEMENT。
  • WHEN ( condition )：触发条件，只有当条件为真时，触发器才会被触发。这是一个可选的子句。
  • EXECUTE PROCEDURE function_name ( arguments )：指定触发器执行的函数及其参数。这是触发器最核心的部分，函数里面定义了触发器要执行的具体操作。

触发器的实际应用场景

接下来，让我们看看触发器在实际业务场景中的应用，这可是干货满满的环节！

1. 数据审计

数据审计是许多企业的重要需求，用于记录数据的变更历史，以便追溯数据来源、发现潜在问题等。触发器是实现数据审计的绝佳工具。

场景： 假设你有一个 users 表，需要记录用户信息的变更历史。你可以创建一个审计表 users_audit，用于存储变更记录。

代码示例：

-- 创建审计表
CREATE TABLE users_audit (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    operation VARCHAR(10),
    modified_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    old_data JSONB,
    new_data JSONB
);
 
-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO users_audit (
            user_id, 
            operation, 
            old_data, 
            new_data
        )
        VALUES (
            OLD.id,
            TG_OP,
            row_to_json(OLD),
            row_to_json(NEW)
        );
        RETURN NEW;  -- 对于 AFTER 触发器，必须返回 NEW 或 OLD
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO users_audit (
            user_id,
            operation,
            old_data
        )
        VALUES (
            OLD.id,
            TG_OP,
            row_to_json(OLD)
        );
        RETURN OLD;  -- 对于 AFTER 触发器，必须返回 NEW 或 OLD
    END IF;
    RETURN NULL;  -- 对于其他操作，返回 NULL
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE
ON users
FOR EACH ROW
EXECUTE PROCEDURE audit_user_changes();

解释：

  • 我们创建了一个名为 audit_user_changes 的函数，用于记录变更信息。
  • TG_OP 变量表示触发的操作，例如 'INSERT', 'UPDATE', 'DELETE'。
  • OLD 和 NEW 分别代表旧数据和新数据。在 INSERT 操作中，OLD 为 NULL；在 DELETE 操作中，NEW 为 NULL；在 UPDATE 操作中，OLD 和 NEW 都有值。
  • row_to_json() 函数将行数据转换为 JSON 格式，方便存储和查询。
  • 我们在 users 表上创建了一个 AFTER 触发器，在 INSERT、UPDATE 或 DELETE 操作之后，都会触发 audit_user_changes 函数。
  • 注意 RETURN NEW 和 RETURN OLD 的使用，在 AFTER 触发器中，这两个返回值是必须的，且分别对应于 INSERT 和 UPDATE 操作返回 NEW，DELETE 操作返回 OLD。

使用方法：

  1. 向 users 表插入数据，例如：

    INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

  2. 更新 users 表中的数据，例如：

    UPDATE users SET email = 'zhangsan.new@example.com' WHERE name = '张三';

  3. 删除 users 表中的数据，例如：

    DELETE FROM users WHERE name = '张三';

  4. 查询审计表 users_audit，查看变更记录：

    SELECT * FROM users_audit;

这样，你就能看到详细的数据变更历史了。

2. 数据同步

数据同步是指将数据从一个数据库或表同步到另一个数据库或表。触发器可以用于实现数据的实时同步。

场景： 假设你有一个主数据库，和一个从数据库。你需要将主数据库中 products 表的数据同步到从数据库的 products 表。

代码示例（简化版）：

-- 假设从数据库的连接信息已经配置好
 
-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_products()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        -- 插入数据到从数据库
        PERFORM dblink_exec('dbname=从数据库名称 user=用户名 password=密码 host=IP地址 port=端口', 
            'INSERT INTO products (id, name, price) VALUES (' || NEW.id || ', ''' || NEW.name || ''', ' || NEW.price || ')');
    ELSIF (TG_OP = 'UPDATE') THEN
        -- 更新从数据库的数据
        PERFORM dblink_exec('dbname=从数据库名称 user=用户名 password=密码 host=IP地址 port=端口', 
            'UPDATE products SET name = ''' || NEW.name || ''', price = ' || NEW.price || ' WHERE id = ' || OLD.id);
    ELSIF (TG_OP = 'DELETE') THEN
        -- 删除从数据库的数据
        PERFORM dblink_exec('dbname=从数据库名称 user=用户名 password=密码 host=IP地址 port=端口', 
            'DELETE FROM products WHERE id = ' || OLD.id);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER products_sync_trigger
AFTER INSERT OR UPDATE OR DELETE
ON products
FOR EACH ROW
EXECUTE PROCEDURE sync_products();

解释：

  • 我们使用了 dblink 扩展，用于连接到从数据库。你需要确保在你的 PostgreSQL 数据库中安装了 dblink 扩展（CREATE EXTENSION dblink;）。
  • dblink_exec() 函数用于在从数据库上执行 SQL 语句。
  • 在 sync_products 函数中，我们根据触发的操作（INSERT、UPDATE、DELETE）执行相应的 SQL 语句，将数据同步到从数据库。
  • 注意，这只是一个简化的示例，实际应用中可能需要考虑更复杂的同步逻辑，例如错误处理、并发控制等。

使用方法：

  1. 向主数据库的 products 表插入、更新或删除数据。
  2. 数据会自动同步到从数据库的 products 表。

3. 数据校验

数据校验是指在数据插入或更新之前，对数据进行检查，确保数据符合业务规则。

场景： 假设你有一个 orders 表，其中包含一个 total_amount 字段，表示订单总金额。你需要确保订单总金额不能为负数。

代码示例：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_order_amount()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total_amount < 0 THEN
        RAISE EXCEPTION '订单总金额不能为负数!';  -- 抛出异常，阻止数据插入或更新
    END IF;
    RETURN NEW;  -- 对于 BEFORE 触发器，必须返回 NEW 或 OLD
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER check_order_amount_trigger
BEFORE INSERT OR UPDATE
ON orders
FOR EACH ROW
EXECUTE PROCEDURE check_order_amount();

解释：

  • 我们创建了一个名为 check_order_amount 的函数，用于检查订单总金额。
  • 在函数中，我们检查 NEW.total_amount 是否小于 0。如果是，则使用 RAISE EXCEPTION 抛出异常，阻止数据的插入或更新。
  • 我们在 orders 表上创建了一个 BEFORE 触发器，在 INSERT 或 UPDATE 操作之前，都会触发 check_order_amount 函数。
  • 注意，对于 BEFORE 触发器，必须返回 NEW 或 OLD。如果返回 NULL，则会导致数据变更被忽略。

使用方法：

  1. 尝试向 orders 表插入或更新一个总金额为负数的订单，例如：

    INSERT INTO orders (total_amount) VALUES (-100);  -- 将会抛出异常

  2. 你将会看到类似以下的错误信息：

    		ERROR:  订单总金额不能为负数!
    		CONTEXT:  PL/pgSQL function check_order_amount() line 3 at RAISE

4. 自动生成序列号

在某些场景下，你需要为表中的某个字段自动生成序列号。触发器可以轻松实现这个功能。

场景： 假设你有一个 products 表，需要为每个产品自动生成一个唯一的 product_code，格式为 PROD-XXXX，其中 XXXX 是一个自增的数字。

代码示例：

-- 创建序列
CREATE SEQUENCE product_code_seq START WITH 1;
 
-- 创建触发器函数
CREATE OR REPLACE FUNCTION generate_product_code()
RETURNS TRIGGER AS $$
BEGIN
    NEW.product_code := 'PROD-' || LPAD(nextval('product_code_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER generate_product_code_trigger
BEFORE INSERT
ON products
FOR EACH ROW
WHEN (NEW.product_code IS NULL)
EXECUTE PROCEDURE generate_product_code();

解释：

  • 我们首先创建了一个序列 product_code_seq，用于生成自增的数字。
  • 在 generate_product_code 函数中，我们使用 nextval('product_code_seq') 获取下一个序列值，并将其格式化为 PROD-XXXX 的形式，然后赋值给 NEW.product_code。
  • 我们在 products 表上创建了一个 BEFORE 触发器，在 INSERT 操作之前，都会触发 generate_product_code 函数。WHEN (NEW.product_code IS NULL) 子句确保只有当 product_code 为 NULL 时，才执行生成逻辑。

使用方法：

  1. 向 products 表插入数据，不需要提供 product_code 字段的值，例如：

    INSERT INTO products (name, price) VALUES ('产品A', 100);  -- product_code 会自动生成

  2. 查询 products 表，查看自动生成的 product_code：

    SELECT * FROM products;

5. 实现复杂的业务规则

触发器可以用于实现复杂的业务规则，例如，当某个字段的值发生变化时，自动更新其他字段的值。

场景： 假设你有一个 orders 表，其中包含 quantity（数量）和 price（单价）字段。你需要实现一个业务规则：当 quantity 或 price 发生变化时，自动更新 total_amount（总金额）字段。

代码示例：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_order_total_amount()
RETURNS TRIGGER AS $$
BEGIN
    NEW.total_amount := NEW.quantity * NEW.price;  -- 计算总金额
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER update_order_total_amount_trigger
BEFORE INSERT OR UPDATE
ON orders
FOR EACH ROW
WHEN (NEW.quantity IS NOT NULL AND NEW.price IS NOT NULL)
EXECUTE PROCEDURE update_order_total_amount();

解释：

  • 我们创建了一个名为 update_order_total_amount 的函数，用于计算总金额。
  • 在函数中，我们使用 NEW.quantity * NEW.price 计算总金额，并赋值给 NEW.total_amount。
  • 我们在 orders 表上创建了一个 BEFORE 触发器，在 INSERT 或 UPDATE 操作之前，都会触发 update_order_total_amount 函数。WHEN (NEW.quantity IS NOT NULL AND NEW.price IS NOT NULL) 子句确保只有当 quantity 和 price 都不为 NULL 时，才执行计算逻辑。

使用方法：

  1. 向 orders 表插入或更新数据，不需要提供 total_amount 字段的值，例如：

    		INSERT INTO orders (quantity, price) VALUES (2, 50);  -- total_amount 会自动计算
    		UPDATE orders SET quantity = 3 WHERE id = 1;  -- total_amount 会自动更新

  2. 查询 orders 表，查看自动更新的 total_amount：

    SELECT * FROM orders;

触发器的注意事项

虽然触发器功能强大，但也需要谨慎使用，避免带来负面影响。以下是一些需要注意的事项：

  1. 性能影响： 触发器会在数据变更时自动执行，如果触发器的逻辑过于复杂或者频繁触发，可能会对数据库性能产生影响。因此，在设计触发器时，应该尽量优化代码，避免不必要的计算。
  2. 维护困难： 触发器的逻辑分散在数据库中，如果触发器过多，或者逻辑过于复杂，可能会导致维护困难。因此，应该对触发器进行良好的组织和文档化。
  3. 递归触发： 触发器可能会引发递归触发，例如，一个触发器修改了数据，导致另一个触发器被触发，然后又触发了第一个触发器。这可能会导致无限循环，甚至导致数据库崩溃。因此，在设计触发器时，需要仔细考虑递归触发的可能性，并采取措施避免。
  4. 事务控制： 触发器是在数据库事务中执行的。如果触发器内部发生错误，可能会导致整个事务回滚，从而影响数据一致性。因此，在编写触发器代码时，需要进行错误处理，并考虑事务控制。
  5. 触发器顺序： 同一事件可以触发多个触发器。PostgreSQL 允许你控制触发器的执行顺序，可以通过 CREATE TRIGGER ... PRIORITY 子句来设置触发器的优先级。理解触发器的执行顺序对于保证业务逻辑的正确性非常重要。
  6. 测试： 在将触发器部署到生产环境之前，一定要进行充分的测试，确保触发器的功能正常，并且不会对数据库性能产生负面影响。

总结

触发器是 PostgreSQL 数据库中一个非常强大的功能，可以用于实现各种复杂的业务需求，例如数据审计、数据同步、数据校验、自动生成序列号等等。通过本文的介绍，相信你已经对触发器有了更深入的了解。记住，在使用触发器时，一定要谨慎，充分考虑性能、维护、递归触发、事务控制等因素。希望这篇文章对你有所帮助，祝你在 PostgreSQL 的世界里玩得开心！

如果你还有其他关于 PostgreSQL 的问题，欢迎随时提问，我会尽力解答！

数据库老司机 PostgreSQL触发器数据库数据审计数据同步

评论点评

打赏赞助
sponsor

感谢您的支持让我们更好的前行

分享

QRcode

https://www.webkt.com/article/7699