PostgreSQL 触发器
PostgreSQL **触发器（Trigger）** 是一种数据库对象，用于在表上发生特定事件（如 `INSERT`、`UPDATE`、`DELETE`）时**自动执行**一段函数（称为触发器函数）。它常用于：
– 数据完整性校验
– 审计日志记录
– 自动更新相关表
– 复杂业务逻辑
—
## 一、触发器基本语法
“`sql
CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE } [OR …]
ON table_name
[ FOR [EACH] { ROW | STATEMENT } ]
[ WHEN (condition) ]
EXECUTE FUNCTION function_name(arguments);
“`
—
## 二、触发器函数（必须先创建）
触发器执行的是一个**返回 `trigger` 类型的函数**。
### 示例：记录操作日志
“`sql
— 1. 创建日志表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT,
operation CHAR(1), — I/U/D
changed_by TEXT,
changed_at TIMESTAMP DEFAULT NOW(),
old_data JSONB,
new_data JSONB
);
— 2. 创建触发器函数
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO audit_log(table_name, operation, changed_by, new_data)
VALUES (TG_TABLE_NAME, ‘I’, CURRENT_USER, to_jsonb(NEW));
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO audit_log(table_name, operation, changed_by, old_data, new_data)
VALUES (TG_TABLE_NAME, ‘U’, CURRENT_USER, to_jsonb(OLD), to_jsonb(NEW));
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO audit_log(table_name, operation, changed_by, old_data)
VALUES (TG_TABLE_NAME, ‘D’, CURRENT_USER, to_jsonb(OLD));
END IF;
RETURN NULL; — 对于 AFTER 触发器，返回值通常忽略
END;
$$ LANGUAGE plpgsql;
“`
—
## 三、创建触发器
“`sql
— 3. 在 users 表上创建触发器（每次行变化都记录）
CREATE TRIGGER trigger_log_users
AFTER INSERT OR UPDATE OR DELETE
ON users
FOR EACH ROW
EXECUTE FUNCTION log_changes();
“`
—
## 四、常用特殊变量（在触发器函数中可用）
| 变量 | 说明 |
|——|——|
| `TG_TABLE_NAME` | 触发器所在表名 |
| `TG_OP` | 操作类型：`’INSERT’`, `’UPDATE’`, `’DELETE’` |
| `NEW` | 新行（INSERT/UPDATE 时可用） |
| `OLD` | 旧行（UPDATE/DELETE 时可用） |
| `TG_WHEN` | `BEFORE` 或 `AFTER` |
| `TG_LEVEL` | `ROW` 或 `STATEMENT` |
—
## 五、BEFORE vs AFTER
| 类型 | 用途 |
|——|——|
| `BEFORE` | 可修改 `NEW` 记录，适合校验、默认值设置 |
| `AFTER` | 数据已写入，适合记录日志、级联操作 |
### 示例：BEFORE 触发器自动填充创建时间
“`sql
CREATE OR REPLACE FUNCTION set_created_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.created_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_set_created_at
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_created_at();
“`
—
## 六、条件触发（WHEN）
“`sql
CREATE TRIGGER trigger_log_big_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary AND NEW.salary > 10000)
EXECUTE FUNCTION log_changes();
“`
—
## 七、STATEMENT 级别触发器（较少用）
“`sql
CREATE TRIGGER trigger_daily_cleanup
AFTER INSERT ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION cleanup_temp_data();
“`
—
## 八、查看与删除触发器
“`sql
— 查看所有触发器
SELECT * FROM pg_trigger WHERE tgrelid = ‘users’::regclass;
— 查看触发器定义
\d+ users
— 删除触发器
DROP TRIGGER IF EXISTS trigger_log_users ON users;
“`
—
## 九、完整示例：库存自动扣减
“`sql
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
stock INT CHECK (stock >= 0)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(id),
quantity INT
);
— 触发器函数：下单时扣库存
CREATE OR REPLACE FUNCTION deduct_stock()
RETURNS TRIGGER AS $$
BEGIN
UPDATE products
SET stock = stock – NEW.quantity
WHERE id = NEW.product_id;
IF NOT FOUND THEN
RAISE EXCEPTION ‘Product not found’;
END IF;
IF (SELECT stock FROM products WHERE id = NEW.product_id) < 0 THEN
RAISE EXCEPTION ‘Insufficient stock’;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
— 创建触发器
CREATE TRIGGER trigger_deduct_stock
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION deduct_stock();
“`
—
## 十、注意事项
1. **性能影响**：触发器会增加每行操作开销，避免复杂逻辑。
2. **递归触发**：可通过 `pg_trigger_depth()` 防止无限递归。
3. **事务安全**：触发器在事务内执行，异常会回滚。
4. **不能直接 COMMIT/ROLLBACK**。
5. **调试困难**：建议用 `RAISE NOTICE` 输出调试信息。
“`sql
RAISE NOTICE ‘Updating user % from % to %’, NEW.id, OLD.name, NEW.name;
“`
—
## 总结
| 特性 | 说明 |
|——|——|
| 类型 | BEFORE / AFTER / INSTEAD OF |
| 级别 | ROW / STATEMENT |
| 语言 | 通常用 `plpgsql` |
| 用途 | 审计、校验、同步、默认值 |
—
需要我提供一个**完整可运行的 demo 脚本**（含建表 + 触发器 + 测试）吗？