PostgreSQL 触发器

入门

简介

PostgreSQL 的 触发器 Trigger 是一类特殊的数据库对象，在表的 INSERT、UPDATE 或 DELETE 事件发生时，自动执行预定义的函数（触发器函数）。它常用于 数据完整性约束、审计日志、自动计算、复杂的业务逻辑处理等场景

触发器的构成

一个完整的触发器由两个部分组成：

触发器函数（Trigger Function）：触发器执行的具体逻辑，必须返回 TRIGGER 类型

触发器（Trigger）：绑定到表的某个事件上，调用触发器函数

触发器的类型

按照触发时间分类

• BEFORE 触发器（在事件发生前执行）
• AFTER 触发器（在事件发生后执行）
• INSTEAD OF 触发器（替代事件执行，仅适用于视图）

按照触发事件分类

• INSERT 触发器（在插入新数据时触发）
• UPDATE 触发器（在数据更新时触发）
• DELETE 触发器（在数据删除时触发）
• TRUNCATE 触发器（在 TRUNCATE 操作时触发）

按照触发级别分类

• 行级触发器（FOR EACH ROW）：对受影响的每一行数据触发一次
• 语句级触发器（FOR EACH STATEMENT）：对整个 SQL 语句仅触发一次

创建触发器

简介

PostgreSQL 触发器的创建需要两步：

1. 编写触发器函数（必须返回 TRIGGER 类型）
2. 创建触发器 并绑定到表

假设我们有一个 users 表，我们希望在有新用户插入时，自动记录日志到 user_logs 表中

示例 1：审计日志

Step 1: 创建审计日志表

CREATE TABLE user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id UUID,
    action TEXT,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: 编写触发器函数

CREATE OR REPLACE FUNCTION log_user_insert() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (user_id, action)
    VALUES (NEW.id, 'User created');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: 创建触发器

CREATE TRIGGER user_insert_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_insert();

示例 2：自动更新修改时间

假设 users 表中有一个 updated_at 字段，我们希望在用户数据更新时，自动更新 updated_at 时间戳。

Step 1: 在 users 表添加 updated_at 字段

ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Step 2: 创建触发器函数

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: 创建触发器

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

示例 3：防止 DELETE

有时我们不希望某些重要数据被删除，可以通过 BEFORE DELETE** 触发器阻止删除**。

Step 1: 创建触发器函数

CREATE OR REPLACE FUNCTION prevent_delete() RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION '不允许删除数据!';
END;
$$ LANGUAGE plpgsql;

Step 2: 绑定到 users 表

CREATE TRIGGER prevent_users_deletion
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION prevent_delete();

触发器的管理

查看已有触发器

SELECT tgname, relname, tgtype, proname 
FROM pg_trigger 
JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
JOIN pg_proc ON pg_trigger.tgfoid = pg_proc.oid
WHERE NOT tgisinternal;

删除触发器

DROP TRIGGER IF EXISTS user_insert_trigger ON users;

删除触发器函数

DROP FUNCTION IF EXISTS log_user_insert();