# PL/pgSQL 与触发器 · 让数据库自己干活

> PL/pgSQL 是 PostgreSQL 内置的过程语言（Oracle PL/SQL 风格），用它写函数、存储过程和触发器函数。触发器在 INSERT/UPDATE/DELETE 前后自动调用函数：自动维护 updated_at、写审计日志、维护派生字段、跨表约束——把这些逻辑下沉到数据库，应用怎么写都绕不过去。

- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-07-13
- 原文：https://daiw.org/manual/postgresql/plpgsql-triggers
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）

# PL/pgSQL 与触发器 · 让数据库自己干活

到目前为止，数据库在我们眼里还是个「被动」的角色：你发 SQL，它返数据。但 PostgreSQL 能主动干活——用 **PL/pgSQL** 把逻辑写进数据库，再用**触发器**让它在数据变动时自动执行。有些一致性规则，与其指望每个调用方的代码都记得做，不如焊死在数据库层，谁也绕不过。

## PL/pgSQL：数据库里的过程语言

`PL/pgSQL` 是 PostgreSQL 默认自带的过程化语言，语法是 Oracle PL/SQL 的风格（变量、`IF`、`LOOP`、异常处理）。它能写三种东西：**函数**、**存储过程**、**触发器函数**。

先看**自定义函数**——把常用逻辑封装成可复用的单元：

```sql
CREATE FUNCTION discounted(price numeric, rate numeric DEFAULT 0.10)
RETURNS numeric AS $$
BEGIN
  RETURN round(price * (1 - rate), 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT discounted(100);        -- 90.00(默认打 9 折)
SELECT discounted(100, 0.25);  -- 75.00
```

注意那个 `DEFAULT 0.10`——PostgreSQL 的函数支持默认参数、具名参数、甚至同名不同参的重载，表达力接近一门小语言。

## 函数 vs 存储过程：能不能控制事务

PG 11 起区分了**函数(FUNCTION)** 和**存储过程(PROCEDURE)**，核心差别是**事务控制**：

- **函数**：运行在**调用者的事务里**，不能自己 `COMMIT` / `ROLLBACK`。
- **存储过程**：用 `CALL` 调用，**内部可以 `COMMIT` / `ROLLBACK`**，适合「分批处理、每批提交」这种长任务。

```sql
CREATE PROCEDURE restock() LANGUAGE plpgsql AS $$
BEGIN
  UPDATE products SET price = price * 1.1 WHERE name = 'Juice';
  COMMIT;   -- 存储过程里可以提交
  UPDATE products SET price = price * 1.1 WHERE name = 'Milk';
  COMMIT;
END; $$;

CALL restock();   -- Juice: 16.5, Milk: 8.8
```

## 触发器：数据一变，自动执行

触发器(Trigger)让函数在表的 `INSERT` / `UPDATE` / `DELETE` **前后自动触发**。它能：

- **时机**：`BEFORE`（改之前）、`AFTER`（改之后）、`INSTEAD OF`（用于视图）。
- **粒度**：`FOR EACH ROW`（每行触发）、`FOR EACH STATEMENT`（每语句一次）。
- **条件**：`WHEN (...)` 只在满足条件时触发。
- **访问新旧行**：`NEW`（新值）、`OLD`（旧值）。

**用途一：自动维护 `updated_at`**——`BEFORE UPDATE` 里把时间戳塞进 `NEW`：

```sql
CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();   -- 改的是「即将写入」的新行
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_touch BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
-- 之后任何 UPDATE,updated_at 自动刷新,应用层一行都不用写
```

**用途二：自动写审计日志**——`AFTER` 触发，把 `OLD` / `NEW` 记进日志表：

```text
op=INSERT  before=∅   after=∅
op=UPDATE  before=20  after=22    ← 谁把 20 改成了 22,自动留痕
op=DELETE  before=∅   after=∅
```

<Callout type="info">
  `BEFORE` 触发器改 `NEW` 能影响**即将写入的值**（所以 `updated_at` 用 BEFORE）；`AFTER` 触发器改 `NEW` 没用（已经写完了），它适合做「基于最终结果的副作用」——写日志、发通知、更新汇总表。选错时机是新手常见坑：想改值就 BEFORE，想响应结果就 AFTER。
</Callout>

## 一把双刃剑：别把业务全塞进触发器

触发器很强，但强到容易滥用。它的隐患是**「隐式」**——逻辑藏在数据库里，应用代码看不见。一次简单的 `UPDATE` 背后可能悄悄触发一串连锁反应，排查问题时让人摸不着头脑。

<Callout type="warn">
  经验法则：**用触发器做「数据完整性 / 审计 / 派生字段」这类和业务解耦、且必须保证的横切逻辑**（`updated_at`、审计日志、维护 `tsvector`）；**别用它承载核心业务流程**（下单减库存、算优惠这种）——那些放应用层，可见、可测、可版本管理。触发器是「兜底的安全网」，不是「藏起来的业务逻辑」。
</Callout>

## 横向对比

| 能力 | PostgreSQL | MySQL | Oracle | SQL Server |
| --- | --- | --- | --- | --- |
| 过程语言 | PL/pgSQL + PL/Python/Perl/JS 等**多语言** | 存储过程（单一） | PL/SQL（标杆） | T-SQL |
| 触发器函数 | 独立函数，可复用 | 触发器内联，不可复用 | 有 | 有 |
| 存储过程事务控制 | PG 11+ 有 | 有 | 有 | 有 |
| `INSTEAD OF` 触发器 | 有（视图可写） | ❌ 无 | 有 | 有 |

PostgreSQL 有个独特优势：**过程语言可插拔**。除了 PL/pgSQL，还能装 PL/Python、PL/Perl、PL/v8(JavaScript)——用你顺手的语言写数据库函数。MySQL 的存储过程相对弱、触发器逻辑内联不能复用，是它和 PG 差距较明显的一块。

## 应用场景

- **自动时间戳**：`updated_at` / `created_at` 用 BEFORE 触发器维护，免得每个写路径都手动带。
- **审计留痕**：关键表的增删改用 AFTER 触发器写审计表，满足合规要求。
- **派生 / 汇总维护**：子表变动时自动更新父表的计数 / 汇总（注意：简单派生列优先用 [生成列](https://daiw.org/manual/postgresql/range-generated)）。
- **复杂校验**：跨表、跨行的完整性约束，`CHECK` 表达不了的用触发器兜底。
- **批处理任务**：存储过程 + `CALL`，分批提交处理大数据量迁移 / 清洗。

## 小结与下一章

PL/pgSQL 让数据库从「被动应答」变成「能主动执行逻辑」，触发器则让这些逻辑在数据变动时自动生效。用对地方（完整性、审计、派生），它是可靠的安全网；用过头（核心业务），它会变成难以追踪的隐藏逻辑。分寸感很重要。

触发器能在库内响应数据变化，但如果想让**库外的应用**也实时知道「数据变了」呢？下一章的 LISTEN/NOTIFY 让 PostgreSQL 直接当一个轻量消息总线。👉 [LISTEN/NOTIFY：数据库自带的发布订阅](https://daiw.org/manual/postgresql/listen-notify)。

## 参考文献

- [1] PostgreSQL 官方文档.「Chapter 43. PL/pgSQL.」https://www.postgresql.org/docs/current/plpgsql.html —— 过程语言语法、函数、异常处理。
- [2] PostgreSQL 官方文档.「Chapter 39. Triggers.」https://www.postgresql.org/docs/current/triggers.html —— 触发器时机、粒度、`NEW`/`OLD`。
- [3] PostgreSQL 官方文档.「CREATE PROCEDURE.」https://www.postgresql.org/docs/current/sql-createprocedure.html —— 存储过程与事务控制。