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

2025/3/7 06:26:57 197 0 0 0

嘿，老铁们！我是老码农，最近在处理一个棘手的问题，数据库里的数据啊，那是鱼龙混杂，各种奇葩数据都有，简直让人头大。后来，我发现了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 触发器数据校验