PostgreSQL / 系统课时
创建触发器
创建触发器
本课解决“数据变化后必须自动校验或留痕”的问题。你将创建 BEFORE UPDATE 与 AFTER UPDATE 触发器,在余额更新前阻止非法数据,并在更新成功后写入审计记录。
学习目标
- 理解触发器函数与触发器对象的关系
- 区分
BEFORE和AFTER触发器的职责 - 识别触发器在批量写入、排错和性能上的隐式成本
触发器如何工作
PostgreSQL 触发器由两部分组成:触发器函数定义要执行的逻辑,CREATE TRIGGER 决定在什么表、什么事件和什么时机调用该函数。
行级触发器使用 FOR EACH ROW,每一行变化都会执行一次。触发器函数中,OLD 表示变更前的行,NEW 表示变更后的行,TG_OP 表示当前操作类型。
BEFORE 与 AFTER 的选择
BEFORE 在数据真正写入前执行,可以修改 NEW,也可以抛出异常阻止写入,因此适合数据修正与补充校验。AFTER 只在写入成功后执行,不能再改变已保存的行,适合审计、统计或派生记录。
同一需求不要同时放在应用代码和触发器中重复执行,否则容易产生双写。必须保证无论哪个客户端写表都执行的规则,才适合放入触发器。
创建余额校验与审计触发器
以下脚本可在空测试库中直接运行。它创建账户表和审计表,再用两个触发器处理更新前校验与更新后留痕。
DROP TABLE IF EXISTS account_balance_audit;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
id bigint PRIMARY KEY,
owner_name text NOT NULL,
balance numeric(12, 2) NOT NULL,
updated_at timestamptz NOT NULL DEFAULT current_timestamp
);
CREATE TABLE account_balance_audit (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_id bigint NOT NULL,
action text NOT NULL,
old_balance numeric(12, 2) NOT NULL,
new_balance numeric(12, 2) NOT NULL,
changed_at timestamptz NOT NULL DEFAULT current_timestamp
);
CREATE OR REPLACE FUNCTION validate_account_balance()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
-- BEFORE 触发器可以拒绝非法的新行
IF NEW.balance < 0 THEN
RAISE EXCEPTION 'balance cannot be negative';
END IF;
NEW.updated_at := current_timestamp;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION audit_account_balance()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
-- 只在余额确实变化时写入一条审计记录
IF NEW.balance IS DISTINCT FROM OLD.balance THEN
INSERT INTO account_balance_audit (
account_id, action, old_balance, new_balance
)
VALUES (
NEW.id, TG_OP, OLD.balance, NEW.balance
);
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_account_validate_before_update
BEFORE UPDATE ON account
FOR EACH ROW
EXECUTE FUNCTION validate_account_balance();
CREATE TRIGGER trg_account_audit_after_update
AFTER UPDATE ON account
FOR EACH ROW
EXECUTE FUNCTION audit_account_balance();
INSERT INTO account (id, owner_name, balance)
VALUES (1, 'Alice', 1000.00);
UPDATE account
SET balance = balance - 200.00
WHERE id = 1;
SELECT id, balance FROM account;
SELECT action, old_balance, new_balance
FROM account_balance_audit;预期结果
UPDATE 1
id | balance
----+---------
1 | 800.00
action | old_balance | new_balance
--------+-------------+------------
UPDATE | 1000.00 | 800.00客户端显示的边框和空格可能不同,但账户余额应为 800.00,审计表应新增一条从 1000.00 变为 800.00 的记录。
验证失败路径
执行 UPDATE account SET balance = -1 WHERE id = 1; 时,BEFORE 触发器会抛出 balance cannot be negative,整条更新语句回滚,AFTER 触发器不会执行,也不会产生审计记录。
触发器与原语句处于同一事务中。触发器内部任何未处理异常都会使当前语句失败,这保证了一致性,也意味着触发器不应执行不可控的重任务。
隐式成本与使用边界
触发器不会直接出现在应用调用代码中,排错时容易被忽略。行级触发器在一次更新一万行时可能执行一万次,因此要关注索引、触发次数和审计表增长速度。
远程通知、消息重试和跨系统编排不应直接写在触发器中。更稳妥的做法是让触发器或事务写入本地事件表,再由应用异步投递。