一、触发器简介：三种时机，两种粒度

一条 CREATE TRIGGER 要回答三个问题： 什么时候跑（BEFORE／AFTER／INSTEAD OF）、对什么事件（INSERT／UPDATE／DELETE）、 跑几次（语句级／行级）。

BEFORE 和 AFTER 的差别不是「早一点晚一点」，而是「能不能改数据」。 BEFORE 行级触发器能改 :NEW.列，AFTER 不能——因为那时候数据已经写进去了。 粒度只有两种：语句级（一条 DML 跑一次）和行级（每一行跑一次）。 FOR EACH ROW 就是行级，不写就是语句级。 这个 FOR EACH ROW 是全篇最容易漏的三个词。

二、语句级触发器：一条 DML 跑一次

不写 FOR EACH ROW 就是语句级。 一条 update 改了三千行，它也只跑一次。 代价是它拿不到行数据。 :OLD 和 :NEW 在语句级触发器里根本不存在， 所以它只能知道「有人改了 emp」，不知道「改了谁、改成什么」。 典型用途是「汇总型日志」： 记一条「某人在某时对 emp 做了一次 update」，不记具体行。 这种日志量小、写入快，对性能几乎没影响，适合只想留个痕迹的场合。 还有一个用处是「禁止某类操作」。 比如在语句级 BEFORE DELETE 里直接 RAISE_APPLICATION_ERROR， 整条 delete 就被挡住了——连一行都不用扫。

三、行级触发器：每一行跑一次，能拿到新旧值

加了 FOR EACH ROW，:OLD 和 :NEW 就可用了。 :OLD.列 是改之前的值，:NEW.列 是改之后的值。

WHEN 子句可以再加一道条件，不满足就整个跳过。 WHEN (NEW.sal < OLD.sal * 0.5) 读起来比在 BEGIN 里写 IF 清楚， 而且不满足条件时连触发器体都不进，省一次上下文切换。 BEFORE 行级触发器可以给 :NEW.列 赋值，这是它独有的一种用法：自动补值。 比如「插入时 :NEW.create_time := SYSDATE」，应用端就不用管这列了。 AFTER 行级不能改 :NEW——数据已经写进去了，改也没用。 它适合做「已经发生的事」的记录：往日志表插一行、往汇总表累加。

四、替换触发器：让视图可写

视图本身是「一条存起来的查询」，默认只读。 想在视图上做 insert／update／delete，就得建 INSTEAD OF 触发器。 它「代替」你对视图的 DML，让你自己去改底表。 INSTEAD OF 有两个硬限制： ① 只能建在视图上，不能建在表上；② 必须是行级（FOR EACH ROW 必写）。 因为「代替」这个动作，本来就是逐行代替的。 为什么需要它？因为复杂视图根本没法直接改。 带聚合、带 union、带 distinct、带子查询的视图，Oracle 一律视为只读—— 底表的哪一行对应视图的这一行，它算不出来。 这种时候只能你自己写清楚对应关系。 「简单视图」（单表、无聚合、无 distinct）其实能直接改，不用触发器。 教材把 INSTEAD OF 叫「替换触发器」，是强调「替换掉原来的 DML 动作」这层意思。

五、用户事件触发器：登录、DDL 也能触发

前面三种都挂在表或视图上。 还有一类挂在「数据库事件」上，叫用户事件触发器（也叫系统触发器）。

触发器体里可以用几个「属性函数」拿到事件细节： ORA_SYSEVENT 是事件名（CREATE／DROP）、ORA_DICT_OBJ_NAME 是对象名、 ORA_LOGIN_USER 是登录用户。 这三样凑在一起，就是一份「谁在什么时候动了什么」的审计日志。 建这类触发器要 ON DATABASE 或 ON SCHEMA，权限要求比普通触发器高。 而且它作用范围大——ON DATABASE 是全库，写错一个，可能整个库连不上。 所以生产上建之前先在测试库上验，别直接上。

六、删除触发器：先禁用，再决定删不删

DROP TRIGGER trg_sal_check; 删掉。 ALTER TRIGGER trg_sal_check DISABLE; 只是停用，还能 ENABLE 回来。 排查「数据怎么莫名其妙变了」的时候，第一步就是查触发器。 DISABLE 比 DROP 好——不改代码就能验证「是不是它干的」，验完再开回来。 查有哪些触发器：select trigger_name, table_name, status from user_triggers; trigger_body 那一列直接存着触发器干了什么，不用去翻源码。 状态是 ENABLED 还是 DISABLED，一眼就能看出来。 触发器失效会报 ORA-04098（触发器无效且重新验证失败）。 这个错通常意味着它依赖的表或对象被改了， 编译一下触发器（ALTER TRIGGER ... COMPILE）一般就好了。

七、动手 Demo：三种触发器真跑一遍：拦住降薪、记日志、让视图可写

沙箱里没有 Oracle，CREATE TRIGGER ... BEGIN ... END; 这种 PL/SQL 触发器写不了。 但 SQLite 也有触发器，语法更简单，BEFORE／AFTER／INSTEAD OF 三种都在。 我把教材里那三种触发器用 SQLite 的语法重写了一遍，全部真跑： 一条拦住降薪、一条记日志、一条让视图可写。 语义对得上，语法有差别，差别我都标出来了。

import sqlite3

c = sqlite3.connect(':memory:')
c.executescript('''
CREATE TABLE emp (empno INT, ename TEXT, sal REAL, deptno INT);
INSERT INTO emp VALUES
  (7782,'CLARK',2450,10), (7839,'KING',5000,10),
  (7934,'MILLER',1300,10), (7788,'SCOTT',3000,20);
CREATE TABLE audit (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    act TEXT, empno INT, old_sal REAL, new_sal REAL);
''')

# 一、BEFORE 行级：一次降薪超过一半就拦住
c.executescript('''
CREATE TRIGGER trg_sal_check
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN NEW.sal < OLD.sal * 0.5
BEGIN
    SELECT RAISE(ABORT, '一次降薪超过一半，拦住');
END;
''')
try:
    c.execute('update emp set sal = 100 where empno = 7788')
    print('居然改成功了，说明触发器没生效')
except sqlite3.IntegrityError as e:
    print('被拦住：', e)
print('SCOTT 的工资还是', c.execute(
    'select sal from emp where empno = 7788').fetchone()[0])

# 二、AFTER 行级：每改一行，往 audit 写一行
c.executescript('''
CREATE TRIGGER trg_sal_audit
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    INSERT INTO audit (act, empno, old_sal, new_sal)
    VALUES ('update', OLD.empno, OLD.sal, NEW.sal);
END;
''')
c.execute('update emp set sal = round(sal * 1.1, 2) where deptno = 10')
for r in c.execute('select empno, old_sal, new_sal from audit order by id'):
    print(r)

# 三、INSTEAD OF：让视图「可写」
c.executescript('''
CREATE VIEW v_emp10 AS
  SELECT empno, ename, sal FROM emp WHERE deptno = 10;
CREATE TRIGGER trg_v_emp10
INSTEAD OF UPDATE ON v_emp10
FOR EACH ROW
BEGIN
    UPDATE emp SET sal = NEW.sal WHERE empno = OLD.empno;
END;
''')
c.execute('update v_emp10 set sal = 9999 where empno = 7934')
print('底表里的 MILLER ->', c.execute(
    'select sal from emp where empno = 7934').fetchone()[0])

实跑输出：

--------------------------------------------------------------------
第二十八课：触发器——表被改的时候，自动跑一段代码
--------------------------------------------------------------------

  **触发器是「绑在事件上的代码」。**
  **你不用调用它，`insert`／`update`／`delete` 一发生，它自己就跑。**
  **这是它最大的价值，也是最大的危险：**  **看不见的代码最容易被忘掉。**
  **一个跑了五年的触发器，可能只有当初写它的那个人知道它存在。**


  三种触发时机，两种粒度
  --------------------------------------------------------------------

  SQL> select kind as 时机, fires as 什么时候跑, note as 典型用途 from tg
  时机       什么时候跑      典型用途
  ---------------------------------------------------------------
  BEFORE     在 DML 执行之前 校验、改值——能把坏数据挡在外面
  AFTER      在 DML 执行之后 记日志、级联更新——这时候数据已经落定
  INSTEAD OF 代替 DML 执行   只能建在视图上，用来让视图「可写」
  已选择 3 行
  **`BEFORE` 和 `AFTER` 的区别不是「早一点晚一点」，是「能不能改数据」。**
  **`BEFORE` 行级触发器能改 `:NEW.列`，`AFTER` 不能**——  **因为数据已经写进去了。**
  **粒度分两种：语句级（一条 DML 跑一次）和行级（每一行跑一次）。**
  **`FOR EACH ROW` 就是行级，不写就是语句级。**

  第一种：BEFORE 行级触发器做校验
  --------------------------------------------------------------------

  SQL> select count(*) as 触发器建立前的日志行数 from audit
  触发器建立前的日志行数
  ----------------------
  0
  已选择 1 行

  **这条触发器的规则是「一次降薪不能超过一半」。**  **`WHEN` 子句先判条件，不满足就整个跳过**——
  **这样比在 `BEGIN` 里写 `IF` 再 `RAISE` 更省事。**
  **`SCOTT` 现在的工资是 3000。**
  **改成 100 被拦住了：`一次降薪超过一半，拦住`**

  SQL> select empno, ename, sal from emp where empno = 7788
  EMPNO ENAME SAL
  ----------------
  7788  SCOTT 3000
  已选择 1 行
  **工资一点没变。**  **`RAISE(ABORT, ...)` 会把整条 `update` 回滚掉**，不只是跳过这一行。
  **这个语义很重要**：`ABORT` 是「整条语句作废」，不是「这一行跳过」。

  第二种：AFTER 行级触发器记日志
  --------------------------------------------------------------------
  **现在给部门 10 的三个人各涨 10 个点，一条 `update`：**

  SQL> select empno, old_sal, new_sal from audit order by id
  EMPNO OLD_SAL NEW_SAL
  ---------------------
  7782  2450    2695
  7839  5000    5500
  7934  1300    1430
  已选择 3 行
  **一条 `update`，日志里出现了三行。**  **因为这是行级触发器——每改一行就跑一次。**
  **如果写成语句级（Oracle 里不写 `FOR EACH ROW`），只会跑一次，而且那时候你不知道改了哪几行。**
  **这也是「行级」和「语句级」最实际的区别：**  **行级知道 `:OLD` 和 `:NEW`，语句级不知道。**
  **要记「改了哪些行」，只能用行级。**

  第三种：INSTEAD OF 触发器让视图可写
  --------------------------------------------------------------------
  **视图默认是只读的。**  **想通过视图改底表，就得在视图上建 `INSTEAD OF` 触发器**——
  **它「代替」你对视图的 DML，让你自己去改底表。**

  SQL> select * from v_emp10 order by empno
  EMPNO ENAME  SAL
  -----------------
  7782  CLARK  2695
  7839  KING   5500
  7934  MILLER 1430
  已选择 3 行

  **视图上有这条触发器之后，对视图 `update` 就能改到底表了：**

  SQL> select empno, ename, sal from emp where empno = 7934
  EMPNO ENAME  SAL
  -----------------
  7934  MILLER 9999
  已选择 1 行
  **`MILLER` 的工资确实变成了 9999，改的是 `emp` 表。**
  **这条链路是：`update 视图` → 触发器 → `update 底表`。**
  **不用触发器能不能改视图？**
  **能——只要视图是「简单视图」（单表、无聚合、无 `distinct`），Oracle 允许直接改。**
  **一旦视图带聚合、带 `union`、带子查询，就变只读，只能靠 `INSTEAD OF`。**

  触发器也怕「自己调自己」
  --------------------------------------------------------------------
  **触发器的代码里如果又去改触发它自己的那张表，会发生什么？**
  **Oracle 的答案是：直接拦住，报 `ORA-04091`（table is mutating）。**
  **注意这不是「递归太深被掐断」，而是【压根不许这么写】**——
  **行级触发器执行时，那张表正处在「改到一半」的状态，读它都算脏读。**
  **SQLite 不一样：它默认允许触发器改自己的表，只靠 `recursive_triggers` 开关限深度。上面三条触发器之所以两边都能跑，是因为它们写的都是【别的对象】：一条只 `RAISE` 不改表，一条往 `audit` 写，一条在视图上改底表 `emp`——没有一条碰自己那张表。**
  **一旦真的写成「`emp` 的触发器改 `emp`」，Oracle 当场报 `ORA-04091`，连试都试不了。**
  **更常见的坑是「触发器链」：**  **A 表的触发器改 B 表，B 表的触发器又改 A 表。**
  **这种设计一开始能跑，数据量上来之后会变得极慢，而且没人知道为什么。**
  **规矩：触发器只做「轻活」。**  **校验、记日志可以；跑复杂业务、跨多张表改数据，别放触发器里。**

  看有哪些触发器，以及删掉它们
  --------------------------------------------------------------------

  SQL> select name, tbl_name from sqlite_master where type = 'trigger' order by name
  NAME          TBL_NAME
  ----------------------
  trg_sal_audit emp
  trg_sal_check emp
  trg_v_emp10   v_emp10
  已选择 3 行
  **`sqlite_master` 里能看到三条触发器，以及它们挂在哪张表上。**
  **Oracle 里对应的是 `user_triggers`**，
  **里面有 `trigger_body` 列，能直接看到触发器干了什么。**

  [Oracle 原码 · 本沙箱不执行]
  | DROP TRIGGER trg_sal_check;
  | ALTER TRIGGER trg_sal_check DISABLE;
  | ALTER TRIGGER trg_sal_check ENABLE;
    禁用比删除安全，排查时先禁用。

  **排查「数据怎么莫名其妙变了」的时候，第一步就是查触发器。**  **`DISABLE` 比 `DROP` 好**——
  不改代码就能验证「是不是它干的」，验完再 `ENABLE` 回来。

第一段：SCOTT 想从 3000 降到 100，被拦住了。 拦住之后工资还是 3000，一行都没改。 这就是 RAISE(ABORT, ...) 的语义：整条 update 作废，不是跳过这一行。 Oracle 里对应的是 RAISE_APPLICATION_ERROR(-20001, '...')，效果一样：整条语句回滚。 第二段：一条 update 改了部门 10 的三个人，日志里出现三行。 2450→2695、5000→5500、1300→1430，都是涨 10 个点。 三行不是「写了三遍」，是「行级触发器每行跑了一次」—— 如果写成语句级（Oracle 里不写 FOR EACH ROW），这里只会有一行，而且没有新旧值。 第三段：update v_emp10 改的是视图，最后变的是 emp 表里的 MILLER。 链路是：update 视图 → INSTEAD OF 触发器 → update 底表。 视图自己一行数据都没存，它只是「一条存起来的查询」。 两处语法差别要说清楚： ① Oracle 用 :OLD.sal／:NEW.sal，SQLite 写 OLD.sal／NEW.sal，没有冒号； ② Oracle 用 RAISE_APPLICATION_ERROR，SQLite 用 SELECT RAISE(ABORT, '...')。 语义一样，写法不同。 还有一处差别是「能不能用 WHEN」： SQLite 的 WHEN 写在 BEGIN 之前，Oracle 也支持 WHEN，位置相同—— 但 Oracle 的 WHEN 里不能写子查询，SQLite 可以。 这一点教材没提，但真写起来会撞上。

30 秒实验

1. 把 trg_sal_check 的 WHEN NEW.sal < OLD.sal * 0.5 改成 WHEN NEW.sal < OLD.sal * 0.9， 重跑。这次连「降 10 个点」都会被拦住——WHEN 子句就是一道总闸。
2. 把 trg_sal_audit 的 FOR EACH ROW 删掉，重跑。 SQLite 会直接报语法错，因为它的 AFTER 触发器默认就是行级，根本没有语句级这个概念。 Oracle 才有语句级和行级之分。
3. 把视图那段 INSTEAD OF 触发器 DROP 掉，再 update v_emp10，看报什么。 SQLite 会报「view is read-only 或者 cannot modify view」—— 这就回到了「视图默认只读」的原始状态。

常见误区

1. 忘了 FOR EACH ROW，结果把行级当语句级用。 语句级拿不到 :OLD／:NEW，编译就报错。 要记「改了哪些行」，只能用行级。
2. 在触发器里 COMMIT。 直接报 ORA-04092（触发器里不能提交）。 触发器属于引发它的那条语句的事务，提交权不在它手上。
3. 在行级触发器里改「正在被改的那张表」。 报 ORA-04091（table is mutating）。 行级触发器执行时，那张表正处在改到一半的状态，读它都算脏读。
4. 把业务逻辑写进触发器。 触发器只该做「轻活」：校验、记日志。 跨多张表改数据、跑复杂计算，一律别放——一旦出事，没人找得到是它干的。

下一篇讲程序包：把一堆过程、函数、变量装进一个命名空间， 还能分出「公开」和「私有」。这是 Oracle 里组织大规模 PL/SQL 的标准做法。

• ☐ （本篇完，下一篇 6.4 程序包）

相关笔记

• 程序包
• 函数
• 操作数据表
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引