★ FEATURED ARTICLE Oracle里的触发器trigger这东西很多人一开始把它想成“数据库里的监控摄像头”这句话大方向没错但真在数据库战场上摸爬滚打过几年的人听到“触发器”三个字往往会不自觉皱一下眉。它能做到在表上发生插入、更新、删除等操作时自动执行一段PL/SQL代码听起来确实很方便可生产环境里不少让人抓狂的“灵异事件”最后追根溯源都藏在一个不起眼的trigger里。这篇文章就是想把Oracle触发器讲透它到底能解决什么问题有哪些类型怎么写才靠谱生产环境里会踩哪些坑以及什么时候你根本不该用它。内容对于刚接触Oracle的开发同学、日常要维护数据库的DBA、还有正在做数据审计和同步方案的数据工程师都有参考价值。废话不多说我直接按自己实操的积累往下讲。1. 触发器到底是什么解决什么问题1.1 一句话解释触发器用生活里的东西做个类比触发器其实就像一个门铃。你在门口按门铃屋里的人就知道有人来了然后会自动做出回应可能是开门可能是喊一声。触发器也一样你在表上做了一条INSERT操作Oracle就会“按响门铃”自动执行预先写好的那一段PL/SQL代码。从数据库角度定义触发器trigger是一种存储在数据字典里的独立PL/SQL程序单元它和表、视图、数据库事件绑定当指定的事件发生时由数据库自动隐式调用。它跟普通存储过程最大的区别是存储过程得有人主动去CALL触发器不需要它自己“蹦出来”执行。这个“自动、隐式”的特性是触发器的价值也是它最大的危险源。价值在于你可以在应用代码不感知的情况下完成审计、校验、数据同步等操作危险在于一旦触发器里有问题所有走到这张表的操作都会中招而且问题往往特别难定位。1.2 它能解决哪些实际问题结合真实的项目经验触发器最常见的应用场景大概有这么几类。第一类是操作审计与留痕。我做过一个模拟项目X业务部门要求知道员工表里每一次工资调整是谁改的、改之前是多少、改之后是多少、什么时候改的。这种需求如果全靠业务应用去写日志很容易漏而且老系统的应用代码改动成本很高。用触发器挂在员工表上任何针对工资字段的更新都会被记录到一张独立的日志表里无须动业务代码。第二类是复杂完整性约束。Oracle里的CHECK约束能管简单的规则比如年龄大于0但管不了跨行、跨表的规则。比如“订单明细的总金额必须等于订单主表的总金额”这种单靠一个表的CHECK约束搞不定就可以用触发器在明细表上维护主表的汇总值。第三类是派生字段的自动维护。很多系统里会存冗余字段比如订单表里存了商品单价和数量又冗余一个总金额字段。如果靠应用层每次计算再写进去容易出现计算逻辑不一致的问题。通过BEFORE行级触发器自动计算并写入应用层就不用总记着这茬儿了。第四类是数据库层面的统一规则落地。比如所有表插入数据时自动填充创建时间、修改时间所有删除操作都需要先写一条删除原因。诸如此类的基础规则放在触发器里能保证任何入口都绕不开不管是正规接口还是某个维护人员直连数据库手改数据。1.3 为什么DBA对触发器爱恨交加触发器的优点确实明显集中、隐式、安全能保证规则在数据库层面无条件生效。但我在实际工作中见过太多触发器引发的意外总结下来主要是三个问题。一是可观测性差。触发器是隐式执行的一条UPDATE语句看着只影响了一张表实际上背后可能还更新了日志表、发了消息、调了存储过程。没有经验的人排查问题时很难想到去查触发器。二是性能隐患。行级触发器在批量操作时会被执行成千上万次如果触发器内部的逻辑还很重哪怕每条只多几毫秒整体性能影响就会被放大得非常恐怖。三是维护难度高。触发器存储在数据库里很多人不知道它的存在等某天系统报错了才发现原来这里还挂着一个三年前的trigger。数据库表结构一调整触发器往往就变成INVALID状态连带整个DML操作都会失败。所以我现在做方案时对触发器的态度是不排斥但一定要经过充分评估。能用约束解决的事别用触发器能用应用层显式处理的事也尽量别用触发器。真到了非用不可的场景再好好设计它。2. 触发器的分类与运行机制2.1 事件-条件-动作触发器的三段式骨架Oracle里任何触发器都可以拆成三段来理解事件、条件、动作。事件指触发时机和触发操作。操作一般是DML语句比如INSERT、UPDATE、DELETE也可以是DDL操作CREATE、ALTER、数据库级事件数据库启动、登录等。时机分为BEFORE和AFTER也就是操作发生前还是发生后执行触发器。条件指WHEN子句只有满足这个条件动作才会执行。比如你只想在工资大于10000的时候记录日志就可以把判断条件写在WHEN里。这样数据库在内部判断条件不满足时连PL/SQL块的执行上下文都不会进入性能上会好很多。动作就是真正要执行的PL/SQL代码块可以包含变量声明、异常处理、数据操作、调用存储过程等等。用伪代码来表示就是CREATE OR REPLACE TRIGGER 触发器名 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 [FOR EACH ROW] [WHEN (条件)] DECLARE -- 变量声明 BEGIN -- 执行动作 END;注意FOR EACH ROW是可选的有没有这一行决定了触发器是行级还是语句级差别非常大。2.2 行级和语句级的区别别再混淆很多初学者在这里被绕晕。简单说如果一条UPDATE语句影响了1000行不写FOR EACH ROW的语句级触发器只执行一次写了FOR EACH ROW的行级触发器会执行1000次。语句级触发器适合做“这类操作发生了我需要处理一次”的需求比如记录一条操作日志统计今天这张表发生过多少次修改。它不关心具体是哪行数据被改了。行级触发器适合做逐行校验、逐行记录变化的需求比如检查每一行的工资是否合法、把每一行的旧值和新值都写进审计表。对比项语句级触发器行级触发器触发次数每条DML语句只触发1次每行数据变化触发1次关键语法不加FOR EACH ROW加FOR EACH ROW能否访问:NEW/:OLD不能直接访问可以访问典型场景操作级审计、防误操作字段校验、数据留痕、同步关联表性能影响相对较小批量操作时影响极大我要特别提醒一点当你需要访问:NEW和:OLD这两个系统变量时必须写成行级触发器。这一点几乎是写审计算法时的基本前提后面会专门展开讲。2.3 按触发主体分类Oracle的触发器按触发主体划分主要有三类。DML触发器是最常用的挂在表上对INSERT、UPDATE、DELETE操作生效。前面说的审计、字段维护、业务校验基本都是这一类。INSTEAD OF触发器比较特殊它挂在视图上。因为视图本身不是一个实体表很多DML操作无法直接在视图上执行INSTEAD OF触发器就扮演了“视图操作的转译器”你把对视图的INSERT写进触发器在触发器内部再把它转换成对底层表真正的操作。这在一些规范化设计的系统里很常见。系统触发器则绑定数据库实例级或模式级的事件比如DDL事件CREATE、ALTER、DROP、用户登录登出LOGON、LOGOFF、数据库启动与关闭STARTUP、SHUTDOWN。这类触发器通常由DBA用来做安全监控或操作审计。触发器类型可绑定对象常见使用场景DML触发器表、视图配合INSTEAD OF审计、校验、字段维护、同步INSTEAD OF触发器视图复杂视图的数据写入转换系统触发器数据库、SchemaDDL操作审计、登录日志、启动通知3. 手把手从零写一个能落地的触发器3.1 语法骨架与第一个实例真正动手写触发器前建议先搭一个最小的骨架跑通再逐步加逻辑。下面这个例子是做操作审计我把完整过程拆开讲。假设有员工表empCREATE TABLE emp ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), salary NUMBER );再建一张审计日志表emp_audit用来记录员工表上的每次DML操作CREATE TABLE emp_audit ( audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, emp_id NUMBER, old_salary NUMBER, new_salary NUMBER, action_type VARCHAR2(10), op_time TIMESTAMP DEFAULT SYSTIMESTAMP );然后创建行级审计触发器CREATE OR REPLACE TRIGGER trg_emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW DECLARE v_action VARCHAR2(10); BEGIN IF INSERTING THEN v_action : INSERT; ELSIF UPDATING THEN v_action : UPDATE; ELSIF DELETING THEN v_action : DELETE; END IF; INSERT INTO emp_audit (emp_id, old_salary, new_salary, action_type) VALUES (:NEW.emp_id, :OLD.salary, :NEW.salary, v_action); END;几个关键点说一下。IF INSERTING、IF UPDATING、IF DELETING这三个条件判断是Oracle触发器里内置的“状态开关”用来识别当前是什么操作比在多个触发器之间拆分逻辑要省事得多这也是我推荐把INSERT、UPDATE、DELETE合并到一个触发器里的原因之一。AFTER表示在DML操作完成之后记录日志这样做的好处是即使前面还有其他约束拦截也不会留下“虚假日志”。比如插入的数据因为违反主键被拒那触发器根本不会执行日志表里就不会有污染数据。3.2 :NEW和:OLD到底怎么用:NEW和:OLD是行级触发器里最容易让人困惑也是最重要的两个变量。简单记忆法:NEW代表“新值”:OLD代表“旧值”。INSERT操作只有新值所以只能用:NEWDELETE操作只有旧值所以只能用:OLDUPDATE操作两者都有:OLD是更新前的值:NEW是更新后的值。如果你在INSERT触发器里去读:OLD结果一定是NULL这种错误排查起来确实头大。还有一个特别容易掉进去的坑在触发器代码主体里引用:NEW和:OLD时都要带冒号但在WHEN子句里写条件时冒号必须去掉。比如下面这段代码CREATE OR REPLACE TRIGGER trg_emp_salary_check BEFORE INSERT OR UPDATE OF salary ON emp FOR EACH ROW WHEN (NEW.salary 0) BEGIN RAISE_APPLICATION_ERROR(-20001, 工资不能为负数); END;这里WHEN条件里写的是NEW.salary而不是:NEW.salary语法规定如此写错会直接编译报错。WHEN子句的好处是条件不满足时Oracle不会进入PL/SQL执行开销更小。能用WHEN做粗筛的就别在代码块里用IF。:NEW最实用的一个场景是给字段赋值。在BEFORE触发器中你可以直接修改:NEW的值这个值最终会被写入表里。比如订单明细表里有数量qty和单价price希望总金额total自动计算CREATE OR REPLACE TRIGGER trg_order_item_total BEFORE INSERT OR UPDATE OF qty, price ON order_items FOR EACH ROW BEGIN :NEW.total : :NEW.qty * :NEW.price; END;这里之所以用BEFORE而不是AFTER道理很简单我们需要在数据真正入库前把total算出来。AFTER触发器修改:NEW是不起作用的因为数据已经写进去了。3.3 从零搭一个审计需求完整验证我在某公司的项目里就接过一个类似的审计需求。业务方要求记录员工薪资调整的全过程但生产用的业务系统比较老旧没法快速改造应用代码。这时候触发器几乎是唯一的选择。具体实现步骤大概是这样的。第一步确认审计粒度。业务方关心的是“薪资”字段的变化所以触发器只需要在salary字段被修改的时候记录其他字段的变动不用管。这样可以缩小触发范围减少不必要的开销。写法上用的是UPDATE OF salary只有显式更新salary列时才触发避免了所有UPDATE都进触发器的尴尬。第二步创建视图或直接使用日志表把审计信息拆成操作前后对比。我在模拟项目X里建了一张日志表字段包括员工ID、旧值、新值、操作类型、操作时间外加一个可追溯的操作人字段。注意Oracle中要拿当前数据库会话用户可以直接用USER函数如果业务系统有自己的账号体系则需要额外的参数传递机制这个要根据实际环境设计。第三步测试。插一条数据再把工资从10000改成15000最后删除该数据。查日志表应该看到三条记录分别对应INSERT、UPDATE、DELETE。我实操时经常发现有人漏掉DELETING分支导致删除操作没有留下任何记录日志表里只显得单薄不完整。第四步还要做边界测试。试试插入一条salary为NULL的记录触发器会把NULL当成一个普通值处理。如果你不想让NULL进入审计表就得在触发器里用NVL包一层。我一般都会在写触发器时把这些“环境杂音”处理干净避免审计数据里全是NULL干扰业务判断。4. 生产环境踩坑记录与排查技巧4.1 ORA-04091变异表问题ORA-04091是Oracle触发器里的经典大坑错误信息大致是table xxx is mutating, trigger/function may not see it。意思是表正在变化中你的行级触发器不能再去查询或修改触发它的那张表。举个例子我在员工表上写了一个行级触发器目的是确保“部门里的总工资不能超过某个上限”于是在触发器内部去查了一下emp表的总工资CREATE OR REPLACE TRIGGER trg_emp_dept_limit BEFORE INSERT OR UPDATE OF salary ON emp FOR EACH ROW DECLARE v_total NUMBER; BEGIN SELECT SUM(salary) INTO v_total FROM emp WHERE dept_id :NEW.dept_id; IF v_total 100000 THEN RAISE_APPLICATION_ERROR(-20002, 部门工资总额超限); END IF; END;这段代码在编译时没问题但一执行DML就会报ORA-04091。原因在于触发器正在处理emp表的某行数据此时表上的数据处于“变卦”状态Oracle不允许你在同一个会话里去读它否则结果会不稳定可能出现幽灵数据或漏看数据。解决变异表问题有几种常见思路。第一种是改成compound触发器。复合触发器可以把语句级和行级逻辑结合起来先通过行级部分收集涉及到的部门ID再在语句级部分去统一查询和校验避开了“正在变化的表不能读”的矛盾。第二种是借助临时包变量。在行级触发器里只把这个部门的ID存到包变量里不查表然后再用一个语句级AFTER触发器统一去检查。这也是比较常见的做法但实现起来稍显繁琐。第三种是从设计上避开。既然行级触发器里不能查本表那就把校验逻辑挪到语句级或者干脆用物化视图、约束、应用层逻辑来处理。反正硬碰硬去解ORA-04091多半会越搞越复杂。4.2 性能行级触发器如何把在线业务拖垮我见过一个真实案例某业务系统本来一条UPDATE只需要几十毫秒挂了一个行级触发器后批量更新几千行直接超时。排查下来发现触发器里有一条很重的SQL每条都去查一个大表等于几千行数据每行做一次额外查询性能自然崩了。行级触发器的性能问题本质上是个“放大效应”。一次更新10000行行级触发器的PL/SQL体就被执行10000次。哪怕单次执行只要1毫秒总耗时也会增加10秒这还没算SQL解析、上下文切换的开销。应对手段主要有四个。一是能用WHEN条件过滤就尽早过滤。WHEN子句是数据库层面的判断不满足条件的行根本不会进入PL/SQL执行能省一大截开销。比如只对salary10000的记录做处理那大部分行会被直接绕过。二是触发器内部逻辑要尽量轻量。不要在里面做复杂的查询尤其不要做全表扫描。如果确实需要根据某个关联表取值可以考虑把关联数据提前缓存或者用DML语句直接更新而不是逐行查。三是尽量少用行级触发器改用语句级触发器加批量处理。比如要统计“本月发生过多少条订单修改”语句级触发器就够了根本不需要每行都触发一次。四是监控时别被误导。一次简单UPDATE的执行计划里看不到触发器内部的SQL需要开启SQL Trace或者通过V$SQL里查递归调用来定位。如果在V$SQL里看到很多一模一样的INSERT语句短时间大量出现很可能就是某个表上的行级触发器在拼命写日志。4.3 递归触发与重入问题触发器里最隐蔽的问题是递归触发。A表上有个触发器在A表更新时又会去更新B表B表上如果恰好也有个触发器在B表更新时又回来更新A表那就会形成触发器的递归环轻则无限循环重则导致Oracle报“maximum number of recursive SQL levels exceeded”。最简单的直接递归场景是你在一张表的触发器里继续UPDATE同一张表。比如emp表有个BEFORE触发器每次salary变更后你想顺手把bonus也改掉结果这又是一个对emp表的UPDATE于是再次触发了触发器本身CREATE OR REPLACE TRIGGER trg_emp_recursive BEFORE UPDATE OF salary ON emp FOR EACH ROW BEGIN UPDATE emp SET bonus :NEW.salary * 0.1 WHERE emp_id :NEW.emp_id; END;这段触发器一旦执行几乎必然导致递归错误。正确做法是如果必须更新同表建议换成复合触发器或者包变量来辅助控制用FLAG标识“这次的UPDATE是触发器内部发起的”从而避免再次触发。会话层面还有一个参数可以控制递归触发器ALTER SESSION SET RECURSIVE_TRIGGERS FALSE。但我建议不要把这个当银弹生产环境里改会话参数影响面太大而且不同版本的行为不完全一致。最可靠的办法还是从设计上切断递归路径。顺便提一下级联触发器也是一类隐蔽重入问题。比如emp表更新后写日志表emp_audit而emp_audit表上恰好有个触发器也去更新emp表这种跨表递归的链路更长排查时要靠查询DBA_TRIGGERS把所有相关表上的触发器都列出来才能看清全貌。4.4 排查“数据神秘被改”的实战方法有一次某系统反馈订单表里的状态字段经常无缘无故从“待支付”变成“已取消”但应用系统里没人操作。我排查的第一步就是查这个表上有没有触发器SELECT trigger_name, status, trigger_type, triggering_event, table_name FROM user_triggers WHERE table_name ORDERS;果然发现一个很老的触发器trg_order_cancel_flag这个触发器在某种条件下会自动更新状态字段。后来翻了代码发现是早期做需求时某个开发者写进去的当时的应用场景早已下线但触发器还留在库里一直“勤勤恳恳”地干活。这个案例给我的教训很深任何排障流程的第一步都应该查一下目标表上是否存在触发器尤其是那些自动修改数据的问题。如果在开发环境里还可以通过对比代码版本找原因在生产环境里触发器就是“看不见的手”必须先把它揪出来。常见排查SQL我整理了一下排查目标SQL查看表上的触发器SELECT * FROM USER_TRIGGERS WHERE TABLE_NAME 表名;查看所有触发器状态SELECT OWNER, TRIGGER_NAME, STATUS FROM ALL_TRIGGERS;查看触发器源码SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME 触发器名;查看无效触发器SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS WHERE STATUS INVALID;另外查询DBA_SOURCE可以看触发器编译后的代码但一般USER_TRIGGERS的TRIGGER_BODY就够用了。如果发现触发器状态是INVALID一定要马上处理因为表结构变更后挂在表上的触发器会失效而失效的DML触发器会导致所有DML操作直接报错业务完全不可用。5. 管理触发器与决策经验5.1 触发器的日常运维管理触发器写得出来是一回事管得好是另一回事。我一般在团队里会定这么几条规范。第一命名规范。触发器名字要能一眼看出用途我习惯用trg_表名_业务含义比如trg_emp_salary_audit。千万不要取trg_test_01、trigger_aaa这种名字等半年后回来看根本不知道它是干嘛的也没人敢删。第二启用和禁用要留痕。禁用触发器不是DBA随口说一句的事要记录时间、原因、操作人。尤其是生产环境的触发器禁用了之后往往就没人想起来重新启用审计规则等于形同虚设。用下面的SQL启用或禁用触发器ALTER TRIGGER trg_emp_audit ENABLE; ALTER TRIGGER trg_emp_audit DISABLE;如果想一次性禁用某张表上全部触发器可以写ALTER TABLE emp DISABLE ALL TRIGGERS; ALTER TABLE emp ENABLE ALL TRIGGERS;这个操作很方便但也很危险执行前一定要确认没有正在依赖这些触发器的业务流程。第三触发器代码必须纳入版本管理。把创建触发器的SQL脚本和表结构变更脚本放在一起跟随发布流程走避免“生产库某个触发器是某次运维临时手动加的”这种孤儿状态。第四加触发器必须配套监控。我建议至少在触发器里加入时间戳、操作人等信息有条件的系统还可以把关键业务表的触发器日志定期归档防止日志表无限膨胀。5.2 什么时候真的不要用触发器虽然本文讲了很多触发器的写法但我也得说句实在话触发器不是越用越好很多场景根本不该用它。第一种情况是普通的数据默认值。比如插入记录时填充创建时间这种用DEFAULT约束就能解决没必要上触发器。只有你需要在数据库层面做更复杂逻辑时才考虑。第二种情况是纯粹依赖应用逻辑才能判断的规则。触发器是数据库层的东西它拿不到应用层的上下文。比如需要判断“当前登录用户是否有权限修改这条记录”如果权限模型完全在应用里那触发器很难做。第三种情况是业务中高并发、大流量的写操作路径。触发器每行都执行性能放大效应太明显很多团队在核心交易链路里是明确禁止用触发器的。我个人的判断标准是如果这个业务规则能在数据库外解决那就别在库内加触发器如果必须在库级强制保证优先考虑约束、存储过程等更可控的方式实在没得选了再考虑触发器同时必须做好性能测试和监控。5.3 触发器与替代方案做个对比方案优点缺点适用场景约束CHECK、UNIQUE、FK简单可靠、性能好表达能力有限基础数据完整性触发器隐式自动、能写复杂逻辑排查难、性能放大审计、跨表一致性、复杂校验存储过程/API层显式调用、逻辑清晰依赖应用正确调用业务规则明确、可改造应用物化视图/CDC异步解耦、报表友好延迟、额外存储数据同步、统计分析消息队列/事件驱动弹性扩展、业务解耦体系复杂非强一致的异步通知从执行阶段看约束一定是最优先的因为它由数据库内核直接保证。触发器虽然也在数据库里但它本质是PL/SQL逻辑带来了不确定性。如果你的需求用一行CHECK约束能写完但凡有人建议你用触发器都得多想想是不是过度设计。5.4 最后分享一点个人体会用过几年Oracle之后我对触发器的态度慢慢从“这功能真好用、什么都想试试”变成“能不写就不写、要写就得写干净”。这不是保守而是生产环境真的经不起太多“隐形的自动化”。我踩过最深的坑是在一个老项目里接手了一套离职同事写的触发器没文档、没注释、命名混乱触发器里还嵌套调用存储过程。业务一上线某张表的UPDATE速度慢得离谱查了半天才发现是三个触发器叠加在一起每个都做了全表扫描。后来我把这些触发器全部禁用业务瞬间恢复正常之后再一条一条地重构把真正有用的规则保留了下来。如果你现在正准备在一个新项目里引入触发器我建议你多问自己一句这个逻辑真的需要数据库自动执行吗应用层能不能显式处理有没有更简单可靠的替代方案如果答案都是“必须用触发器”那这篇文章里的写法、注意事项和排障方法应该能帮你少走不少弯路。
阅读完成 · 觉得有帮助？
