WEBKT

PostgreSQL 触发器：数据校验的守护神，让你告别脏数据

20 0 0 0

为什么需要数据校验？

PostgreSQL 触发器简介

触发器与数据校验的结合

1. 创建触发器

2. 编写校验函数

3. 创建触发器并关联校验函数

常见的数据校验场景

1. 邮箱格式校验

2. 限制用户输入的字符串长度

3. 级联更新

4. 审计日志

错误处理与异常处理

1. 抛出异常

2. 返回NULL

3. 处理异常

触发器的优缺点

优点：

缺点：

触发器的最佳实践

总结

嘿，老铁们！我是老码农，最近在处理一个棘手的问题，数据库里的数据啊，那是鱼龙混杂，各种奇葩数据都有，简直让人头大。后来，我发现了PostgreSQL触发器这个宝藏，瞬间感觉找到了救星！今天，我就来跟大家分享一下，如何利用PostgreSQL触发器来守护你的数据，让它变得干净、可靠。

为什么需要数据校验？

在开始之前，咱们先来聊聊为啥需要数据校验。简单来说，数据校验就是为了保证数据的质量。试想一下，如果你的数据库里充斥着各种错误、不一致的数据，那你的应用还能正常运行吗？

  • 业务逻辑错误： 用户在输入数据时，可能不小心输错了，或者应用程序的逻辑出现了问题，导致数据不符合业务规则。
  • 数据类型错误： 例如，应该存储数字的字段，却被用户输入了文本，或者长度超出了限制。
  • 完整性约束： 数据库中的外键、唯一性约束等，如果数据不符合这些约束，就会导致数据不一致。
  • 安全问题： 恶意用户可能会通过SQL注入等方式，来篡改数据库中的数据。

数据校验的目的，就是为了避免这些问题的发生，确保数据的准确性、完整性和一致性，从而保障你的业务正常运行。

PostgreSQL 触发器简介

PostgreSQL触发器是一种特殊的存储过程，它会在特定的数据库事件发生时自动执行。你可以把它想象成一个“守门员”，当有人试图修改你的数据时，它就会站出来进行检查。

触发器可以监听的事件包括：

  • INSERT：当向表中插入新数据时触发。
  • UPDATE：当更新表中数据时触发。
  • DELETE：当从表中删除数据时触发。
  • TRUNCATE：当清空表时触发。

触发器可以在这些事件发生之前（BEFORE）、之后（AFTER）或者替换事件（INSTEAD OF）执行。BEFORE触发器可以用来在数据插入或更新之前进行校验和修改，AFTER触发器可以用来在数据操作之后进行额外的处理，比如更新其他表的数据或者记录日志。INSTEAD OF触发器主要用于视图，可以拦截对视图的DML操作，并进行自定义处理。

触发器与数据校验的结合

触发器最强大的功能之一，就是用于数据校验。你可以通过触发器，在数据被插入或更新之前，对数据进行各种各样的检查，确保它符合你的业务规则。

1. 创建触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF} event
    ON table_name
    [FOR [EACH] {ROW | STATEMENT}]
    EXECUTE FUNCTION function_name (arguments);
  • trigger_name：触发器的名称，必须是唯一的。
  • BEFORE | AFTER | INSTEAD OF：触发器执行的时机。
  • event：触发的事件，可以是INSERT、UPDATE、DELETE或TRUNCATE。
  • table_name：触发器作用的表名。
  • FOR EACH ROW | FOR EACH STATEMENT：触发器的执行频率，FOR EACH ROW表示对每一行数据都触发，FOR EACH STATEMENT表示对整个语句只触发一次。
  • function_name：触发器要执行的函数名，这个函数必须是已定义的，并且返回类型是trigger。
  • arguments：传递给函数的参数，可以为空。

2. 编写校验函数

校验函数是触发器的核心，它负责进行实际的数据校验。校验函数需要接收一个trigger类型的参数，这个参数包含了触发事件的详细信息，例如新旧数据等。

在函数中，你可以访问以下变量：

  • NEW：一个行变量，它包含INSERT或UPDATE操作的新数据。对于UPDATE操作，NEW表示更新后的数据。
  • OLD：一个行变量，它包含UPDATE或DELETE操作的旧数据。
  • TG_OP：一个字符串，表示触发事件的类型，可以是INSERT、UPDATE、DELETE或TRUNCATE。
  • TG_TABLE_NAME：触发器所在的表名。
  • TG_WHEN：触发器执行的时机，可以是BEFORE、AFTER或INSTEAD OF。

下面是一个简单的校验函数示例，用于检查插入数据的年龄是否大于0：

CREATE OR REPLACE FUNCTION check_age()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age <= 0 THEN
        RAISE EXCEPTION '年龄必须大于0';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这个函数首先检查NEW.age的值是否小于等于0，如果是，则抛出一个异常。RETURN NEW表示继续执行INSERT或UPDATE操作，如果返回NULL，则会取消操作。

3. 创建触发器并关联校验函数

将校验函数与触发器关联起来，就可以实现数据校验了。以下是一个完整的例子，用于检查用户表中的年龄字段：

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INTEGER
);
 
-- 创建校验函数
CREATE OR REPLACE FUNCTION check_age()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.age <= 0 THEN
        RAISE EXCEPTION '年龄必须大于0';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER before_insert_users
BEFORE INSERT OR UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION check_age();

在这个例子中，我们创建了一个名为before_insert_users的触发器，它会在users表上进行INSERT或UPDATE操作之前触发。触发器会调用check_age函数，对age字段进行校验。

现在，当你尝试插入或更新年龄小于等于0的数据时，就会触发异常，从而阻止数据被插入或更新。

-- 尝试插入一条非法数据
INSERT INTO users (name, age) VALUES ('张三', -1);
-- 报错：ERROR:  年龄必须大于0
 
-- 尝试更新一条非法数据
UPDATE users SET age = 0 WHERE id = 1;
-- 报错：ERROR:  年龄必须大于0

常见的数据校验场景

数据校验的应用场景非常广泛，下面列举一些常见的场景，供大家参考：

  • 数据类型校验： 确保字段的数据类型正确，例如，检查age字段是否为整数，email字段是否符合邮箱格式。
  • 数据范围校验： 限制字段的取值范围，例如，age字段必须在0到150之间，score字段必须在0到100之间。
  • 数据长度校验： 限制字段的长度，例如，username字段的长度必须小于等于20个字符。
  • 唯一性校验： 确保字段的值在表中是唯一的，例如，username字段不能重复。
  • 关联数据校验： 校验多个字段之间的关系，例如，order表中的total_amount字段必须等于order_items表中所有商品的金额总和。
  • 复杂业务规则校验： 校验复杂的业务规则，例如，判断用户是否满足某个条件才能进行某些操作。

接下来，我将分享一些实际的例子，让大家更深入地了解如何使用触发器进行数据校验。

1. 邮箱格式校验

我们可以使用正则表达式来校验邮箱格式。以下是一个例子：

CREATE OR REPLACE FUNCTION check_email_format()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.email !~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' THEN
        RAISE EXCEPTION '邮箱格式不正确';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER before_insert_users_email
BEFORE INSERT OR UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION check_email_format();

在这个例子中，我们使用了正则表达式^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$来校验邮箱格式。如果email字段不符合这个格式，就会抛出异常。

2. 限制用户输入的字符串长度

CREATE OR REPLACE FUNCTION check_username_length()
RETURNS TRIGGER AS $$
BEGIN
    IF LENGTH(NEW.username) > 20 THEN
        RAISE EXCEPTION '用户名长度不能超过20个字符';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER before_insert_users_username
BEFORE INSERT OR UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION check_username_length();

3. 级联更新

有时候，当某个表的数据发生变化时，需要更新其他表的数据。触发器可以帮助我们实现这种级联更新。例如，当products表中某个商品的价格发生变化时，需要更新order_items表中该商品的价格。

-- 创建商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
 
-- 创建订单明细表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);
 
CREATE OR REPLACE FUNCTION update_order_items_price()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE order_items SET price = NEW.price WHERE product_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER after_update_products_price
AFTER UPDATE
ON products
FOR EACH ROW
EXECUTE FUNCTION update_order_items_price();

在这个例子中，我们创建了一个after_update_products_price触发器，它会在products表中的price字段发生更新后触发。触发器会更新order_items表中对应商品的price字段。

4. 审计日志

触发器还可以用于记录数据的变更日志，方便进行审计和追踪。例如，记录用户表的更新历史。

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INTEGER
);
 
-- 创建用户变更日志表
CREATE TABLE users_audit (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    old_name VARCHAR(50),
    new_name VARCHAR(50),
    old_age INTEGER,
    new_age INTEGER,
    change_time TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users_audit (
        user_id,
        old_name,
        new_name,
        old_age,
        new_age
    )
    VALUES (
        OLD.id,
        OLD.name,
        NEW.name,
        OLD.age,
        NEW.age
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER after_update_users
AFTER UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_changes();

在这个例子中，我们创建了一个after_update_users触发器，它会在users表中的数据发生更新后触发。触发器会向users_audit表中插入一条记录，记录用户的变更信息。

错误处理与异常处理

在触发器中，当数据校验失败时，我们需要进行错误处理。PostgreSQL提供了两种主要的方式来处理错误：

  • 抛出异常： 使用RAISE EXCEPTION语句抛出异常，这将导致INSERT、UPDATE或DELETE操作失败，并回滚事务。这是最常用的错误处理方式，因为它能够确保数据的一致性。
  • 返回NULL： 如果触发器是BEFORE触发器，并且返回NULL，则会取消INSERT或UPDATE操作。

1. 抛出异常

抛出异常是最常用的错误处理方式。当数据校验失败时，你可以使用RAISE EXCEPTION语句抛出异常，并提供一个错误消息。例如：

RAISE EXCEPTION '年龄必须大于0';

抛出异常会中断当前事务，并返回一个错误给客户端。客户端可以捕获这个错误，并进行相应的处理，例如显示错误消息给用户，或者记录错误日志。

2. 返回NULL

对于BEFORE触发器，你可以返回NULL来取消INSERT或UPDATE操作。例如：

RETURN NULL;

这种方式比较简单，但是不如抛出异常灵活，因为它不能提供详细的错误信息。

3. 处理异常

在触发器中，你也可以使用EXCEPTION块来捕获和处理异常。例如，你可以捕获特定的异常，并进行一些自定义的处理，例如记录日志，或者尝试修复错误。

CREATE OR REPLACE FUNCTION check_data()
RETURNS TRIGGER AS $$
BEGIN
    BEGIN
        -- 校验逻辑
        IF NEW.age <= 0 THEN
            RAISE EXCEPTION '年龄必须大于0';
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            -- 记录错误日志
            RAISE NOTICE '数据校验失败: %', SQLERRM;
            -- 返回 NULL，取消操作
            RETURN NULL;
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

在这个例子中，我们使用EXCEPTION块来捕获所有异常（WHEN OTHERS），并记录错误日志。然后，我们返回NULL，取消操作。

触发器的优缺点

触发器作为一种数据校验工具，有其独特的优缺点。了解这些优缺点，可以帮助你更好地选择和使用触发器。

优点：

  • 数据完整性： 触发器可以在数据库层面强制执行数据校验，确保数据的完整性和一致性，避免了应用层面的校验漏洞。
  • 代码复用： 触发器可以将校验逻辑封装在数据库中，可以在多个应用中复用，避免了重复编写校验代码。
  • 自动执行： 触发器可以自动执行，无需在应用层面手动调用，减少了开发工作量。
  • 审计追踪： 触发器可以用于记录数据的变更历史，方便进行审计和追踪。

缺点：

  • 性能开销： 触发器会在数据库事件发生时自动执行，可能会带来一定的性能开销。如果触发器过于复杂或者执行频率很高，可能会影响数据库的性能。
  • 调试困难： 触发器的调试比较困难，因为它的执行是隐式的，不容易追踪错误。
  • 维护复杂： 触发器的数量过多或者逻辑过于复杂，会增加数据库的维护难度。
  • 隐式行为： 触发器的行为是隐式的，可能会导致意想不到的结果，需要仔细设计和测试。

触发器的最佳实践

为了更好地使用触发器，避免潜在的问题，我总结了一些最佳实践，供大家参考：

  • 避免复杂的逻辑： 触发器中的逻辑应该尽量简单，避免复杂的计算和操作，以免影响数据库的性能。
  • 谨慎使用AFTER触发器： AFTER触发器会在数据操作之后执行，可能会导致额外的开销。如果可能，尽量使用BEFORE触发器进行校验和修改。
  • 充分测试： 在将触发器部署到生产环境之前，务必进行充分的测试，确保其功能正确，并且不会影响数据库的性能。
  • 记录日志： 在触发器中记录日志，方便进行调试和追踪。
  • 避免循环触发： 避免触发器之间相互触发，导致无限循环。可以使用条件判断来避免循环触发。
  • 文档化： 对触发器进行详细的文档化，包括触发器的名称、功能、触发时机、校验逻辑等，方便维护和管理。
  • 监控性能： 监控触发器的性能，如果发现性能问题，可以优化触发器的逻辑，或者考虑其他的数据校验方案。
  • 考虑替代方案： 并非所有的数据校验场景都适合使用触发器。在某些情况下，使用应用层面的校验、数据库约束、存储过程等，可能更合适。

总结

PostgreSQL触发器是一个强大的工具，可以帮助你守护数据，提高数据的质量。通过合理地使用触发器，你可以避免脏数据的产生，确保数据的准确性、完整性和一致性。希望今天的分享能帮助你更好地理解和应用PostgreSQL触发器，让你的数据库更加稳定可靠！

最后，我想说，数据校验是一个持续的过程，需要不断地完善和优化。在实际应用中，我们需要根据业务需求，灵活地选择数据校验的方案，并不断地测试和调整。希望大家都能成为数据校验的高手，让我们的数据永远保持干净、健康！

老码农 PostgreSQL触发器数据校验

评论点评

打赏赞助
sponsor

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

分享

QRcode

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