简介《ORACLE PL/SQL 触发器编程篇介绍》是一份面向 Oracle 数据库开发者与管理员的 PL/SQL 触发器专题 PDF 资料系统讲解触发器在补充完整性约束、实现复杂业务规则、完成审计监控等方面的应用并梳理 DML 触发器、INSTEAD OF 触发器、系统触发器、触发条件及行级与语句级触发机制等核心概念。资源仅包含 1 个 PDF 文件压缩包大小约 39KB内容精炼而结构完整适合需要快速掌握 Oracle 触发器编程要点的读者作为随身参考。目前已有 262 人学习下载尤其适合有一定 PL/SQL 基础、希望深入理解触发器创建与执行细节的开发者。资料内含 CREATE TRIGGER、DROP TRIGGER 的完整语法示例覆盖 BEFORE/AFTER 触发时机、WHEN 条件、NEW 与 OLD 记录引用、INSERTING/UPDATING/DELETING 条件谓词等实操细节并配有教师表插入校验与操作日志记录两个典型场景读者可直接对照练习快速迁移到自己的业务逻辑设计中。1. Oracle PL/SQL 触发器不是银弹但能兜住业务规则的底线很多开发者在 PL/SQL 里写触发器都是从“复制一段代码”开始等到线上出现锁等待、莫名其妙的数据不一致才发现问题。Oracle 的 PL/SQL 触发器和存储过程看着像底层机制完全不同——存储过程等你去调用触发器是数据库强行替你执行。这个“自动”既是威力也是风险它能完成完整性约束根本写不出来的复杂业务规则也能在你不注意的时候放大一次普通 UPDATE 的代价。真正做 Oracle 数据库开发和运维的人需要的不只是一段能跑的触发器代码而是搞清楚它的事件模型、粒度差别和触发体里的坑只有这样你的审计、约束逻辑才能稳稳扛住生产环境的操作压力。这篇笔记会把创建、执行、删除和数据字典管理完整过一遍适合正在做 Oracle 开发、或者接手老系统需要读触发器逻辑的人。2. 先弄懂触发器的内部机制事件、对象、时机和粒度四个维度缺一不可2.1 触发器的“四要素”不止是建个名字那么简单Oracle 触发器和普通 PL/SQL 块最大的区别在于它是由事件驱动的。创建一个触发器时必须把事件、对象、时机、粒度这四个维度同时定清楚少了任何一个维度触发器的行为都会偏离你的预期。触发事件就是那三个 DML 单词INSERT、UPDATE、DELETE它们决定了“什么动作能叫醒这个触发器”。触发对象是数据落在哪可以是表、视图也可以是数据库本身。系统触发器的事件是 DDL 语句或者数据库启动关闭这类系统级事件对象直接写 DATABASE 或者 SCHEMA。触发时机是 BEFORE 和 AFTER对应操作前拦截和操作后收尾。粒度是新手最容易忽略、也最容易翻车的维度。“FOR EACH ROW”是行级触发表示 SQL 影响了多少行触发体就执行多少次“FOR EACH STATEMENT”或者干脆不写就是语句级触发一条 SQL 无论更新了 10 行还是 10 万行都只执行一次。我见过有人在行级触发器里写复杂的聚合查询一次 UPDATE 影响 5 万行触发器就被执行 5 万次最后统计信息那分钟数据库几乎锁死。2.2 行级触发与语句级触发NEW 和 OLD 的真实语义搞清楚行级和语句级之后还要搞明白 NEW 和 OLD 这两个记录型变量的含义。在行级触发器里NEW 表示新值OLD 表示旧值。INSERT 操作时没有旧值所以 OLD 里全是 NULLUPDATE 操作时 OLD 是修改前的行NEW 是修改后的行DELETE 操作时没有新值NEW 全是 NULL。这里有个典型的实际用途记录字段修改前的值。比如审计某张业务表的 TNAME 字段你需要在 UPDATE 触发体中写类似 IF :OLD.TNAME ! :NEW.TNAME THEN 的逻辑把变化前后的值成对写入审计表。这是完整性约束做不到的也是审计功能最常见的实现方式。但要注意NEW 和 OLD 只能在行级触发器里用。语句级触发器没有记录级的概念你在这类触发体里写 :NEW 会直接报编译错误这在企业级 PL/SQL 项目里几乎是新人必踩的一道坎。2.3 条件谓词与 WHEN 子句同一个触发器处理三种操作一个触发器可以直接监听 INSERT、UPDATE、DELETE 三种事件但触发体内往往需要区分当前到底是哪种操作。Oracle 为此提供了三个条件谓词INSERTING、UPDATING、DELETING在 PL/SQL 代码里可以像布尔变量一样直接使用。注意 UPDATING 还有一个重载形式 UPDATING(字段名)可以用来判断某个特定字段是否被 UPDATE 语句命中。WHEN 子句则是在触发体执行之前先做一次条件判断条件不满足就直接跳过触发体。这个对性能的好处很直接如果可以前置过滤就不要进触发体再 IF 判断。不过要特别提醒WHEN 子句里引用 NEW 和 OLD 不需要加冒号前缀写成 WHEN (NEW.TNAME David)而进入 PL/SQL 块之后就要写 :NEW.字段名这是个非常容易混淆的语法细节初学者经常在这两处反复切换出错。3. 创建触发器实战从建表到完整例子带异常处理的那种3.1 准备实验环境用户、表和序列的一次性到位动手之前先把实验环境搭好。我一般会在一个独立的测试用户下操作不要直接在生产用户里试。创建测试用户、基础表和审计表整个过程用 SYSTEM 账号执行下面这段脚本-- 创建实验用户并授予基本权限 CREATE USER trig_demo IDENTIFIED BY trig_demo DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CONNECT, RESOURCE TO trig_demo; -- 切换到实验用户后执行 CREATE TABLE teachers ( tid NUMBER PRIMARY KEY, tname VARCHAR2(50) ); CREATE TABLE error_log ( tid NUMBER, err VARCHAR2(200), happen_time DATE DEFAULT SYSDATE ); CREATE TABLE sql_info ( info VARCHAR2(10), happen_time DATE DEFAULT SYSDATE );这段脚本里error_log 用来记录触发器捕获的异常sql_info 用来记录 DML 操作的类型。happen_time 字段加上 DEFAULT SYSDATE这样插入日志时不用显式写时间减少触发体里的代码量。这里是标准的审计表设计手法在生产项目里也经常沿用。3.2 编写第一个触发器阻止重名教师数据的插入核心示例来自原始触发器设计它在插入或更新 David 这个教师名字时进行拦截。完整的创建代码如下CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT OR UPDATE OF tid, tname ON teachers FOR EACH ROW WHEN (NEW.tname David) DECLARE teacher_id teachers.tid%TYPE; insert_exist_teacher EXCEPTION; BEGIN SELECT tid INTO teacher_id FROM teachers WHERE tname NEW.tname; RAISE insert_exist_teacher; EXCEPTION WHEN insert_exist_teacher THEN INSERT INTO error_log(tid, err) VALUES (teacher_id, the teacher already exists!); WHEN NO_DATA_FOUND THEN NULL; END my_trigger; /这段代码的逻辑是当对 teachers 表执行 INSERT或者对 tid、tname 字段执行 UPDATE并且新值中的 tname 为 ‘David’ 时先到表里查一下还有没有同名的教师记录。如果查到说明数据已经存在就抛出异常并在 error_log 表里写一条记录如果查不到NO_DATA_FOUND 异常捕获后不做任何事让 DML 正常通过。这里要注意几个参数BEFORE 表示在 DML 真正执行前拦截此时新数据还没落表所以 SELECT 是查不到当前这条正在插入的记录的除非表里本来就有 DavidFOR EACH ROW 让触发器对每一行受影响的数据都执行判断而不是一条 UPDATE 只判断一次UPDATE OF tid, tname 表示只有当 SQL 语句显式涉及这两个字段时才会触发如果更新的是其他字段触发器不会启动。3.3 第 2 个示例上线用条件谓词记录每次数据库操作第一个例子做的是防御性拦截第二个例子做的是操作审计。如果希望完整记录这张表上每次写操作的类型可以创建下面这个触发器CREATE OR REPLACE TRIGGER my_trigger1 AFTER INSERT OR UPDATE OR DELETE ON teachers FOR EACH ROW DECLARE v_info CHAR(10); BEGIN IF inserting THEN v_info : INSERT; ELSIF updating THEN v_info : UPDATE; ELSE v_info : DELETE; END IF; INSERT INTO sql_info(info) VALUES (v_info); END my_trigger1; /这个触发器选 AFTER是因为它的目的不是拦截操作而是记录操作结果。INSERTING、UPDATING、DELETING 三个谓词把当前操作类型映射成字符串然后写入 sql_info 表。实际项目中这段逻辑可以扩展到记录当前用户、操作时间、受影响行数等信息形成一张完整的操作流水表。这里一个值得留意的细节AFTER 触发的时机在新数据已经落表之后所以如果触发体里再查这张表会查到刚写入的数据而 BEFORE 触发器查不到。做唯一性检查、数据校验这种防御逻辑用 BEFORE 更合适做审计留痕、同步更新汇总数据这类事后逻辑用 AFTER 更顺手。3.4 编译期常见报错和对应修复第一个触发器里用了 SELECT INTO如果表里没有 David 这条记录SELECT 会抛 NO_DATA_FOUND。很多人在最初写这个例子时忘记处理这个异常结果插入非 David 数据没问题插入 David 时表里还没有记录直接报 ORA-01403 未找到数据业务侧看到的就是莫名其妙的报错。另一个高频报错是 ORA-04098触发器无效或未通过重编译。这个错误多数时候不是触发器本身语法错了而是它依赖的存储函数被重新编译过导致触发器失效。解决方式很简单重新执行一次 CREATE OR REPLACE TRIGGER 脚本即可或者用 ALTER TRIGGER 触发器名 ENABLE 重新启用。如果手头没有原始建触发器的脚本可以从数据字典里把源文本捞出来后面会专门讲这个技巧。4. 扩展场景INSTEAD OF 触发器与系统触发器解决视图更新和 DDL 审计4.1 为什么需要 INSTEAD OF绕过视图不可更新这个死结Oracle 里一个很常见的困境是业务上只需要开放一张视图给下游但下游程序要往视图里写数据。普通视图如果是多表连接或者带了聚合直接 UPDATE 是行不通的Oracle 会报 ORA-01779“无法修改与非键值保存表对应的列”。INSTEAD OF 触发器就是为这个场景准备的。它定义在视图上名字已经说明了行为——不执行视图上的原始 DML而是执行你在触发体里写的那套替代逻辑。比如一个教师视图 v_teachers 连接了教师主表和部门表下游执行 INSERT 时你可以把新行拆开分别写进两张基础表。创建这类触发器的语法和 DML 触发器几乎一样只是对象类型必须是视图。我用一个简化场景来说明CREATE OR REPLACE VIEW v_teachers AS SELECT t.tid, t.tname, d.dept_name FROM teachers t, dept d WHERE t.dept_id d.dept_id; CREATE OR REPLACE TRIGGER trg_insert_v_teachers INSTEAD OF INSERT ON v_teachers FOR EACH ROW BEGIN INSERT INTO dept(dept_id, dept_name) VALUES (:NEW.dept_id, :NEW.dept_name); INSERT INTO teachers(tid, tname, dept_id) VALUES (:NEW.tid, :NEW.tname, :NEW.dept_id); END trg_insert_v_teachers; /触发体里的逻辑核心是把对视图的一次 INSERT 拆成对两张基础表两次 INSERT。这里同样要用 :NEW 取新值因为 INSTEAD OF 触发器只存在于行级不需要写 FOR EACH ROW 之外的粒度选择。真正做这种设计时还要考虑主键冲突、外键约束顺序等问题原则就是先插被引用表再插引用表和手工写业务代码的顺序保持一致。4.2 系统触发器把登录和 DDL 操作全部纳入监控系统触发器和 DML 触发器的根本区别在于它的事件对象不是某张表而是模式或者整个数据库。典型的应用场景是记录谁在什么时候执行了 CREATE TABLE、DROP TABLE或者谁在夜间用 sqlplus 登录过数据库。这种监控在等保合规和数据库审计里是硬性需求。看一个实际的登录审计触发器CREATE OR REPLACE TRIGGER trg_login_audit AFTER LOGON ON DATABASE BEGIN INSERT INTO login_history(username, os_user, machine, login_time) VALUES (USER, SYS_CONTEXT(USERENV, OS_USER), SYS_CONTEXT(USERENV, HOST), SYSDATE); END trg_login_audit; /AFTER LOGON ON DATABASE 是系统触发器的固定事件写法行级粒度和 WHEN 条件在这里不再适用。SYS_CONTEXT 是 Oracle 提供的系统上下文函数可以从 USERENV 这个命名空间里取出当前会话的 OS 用户名、客户端主机名等关键审计信息。比起在应用层写登录日志这种方案的强处在于不管用户是从 sqlplus 登录、PL/SQL Developer 登录还是应用连接池登录只要建立了会话触发器就必然执行不存在漏记的可能。生产环境实施时要注意一个问题——如果 login_history 表本身出了问题会导致所有新连接建立失败直接拖垮整个应用。所以这种系统触发器要求表空间、日志表状态的监控必须到位否则就是在给自己埋雷。4.3 系统触发器的风险边界别把审计做成单点故障顺着上面那个风险点再多说几句。数据库级触发器是对所有用户生效的包括 DBA 账号。如果在触发体里执行了某个不存在的存储过程或者引用了已经被删除的表那么任何新建会话都会直接报错谁也登录不进去。这个时候你连 DROP TRIGGER 都执行不了只能先以受限模式启动数据库。所以生产库上部署系统触发器我坚持一个原则触发体里只做最简单、最不可能出错的 INSERT所有可能抛异常的辅助逻辑全部包进异常捕获块里异常发生时直接吞掉也不影响正常登录。审计数据丢一条两条可以补救数据库连不上就是事故。这个取舍对线上系统来说没有第二个选项。5. 触发器常见问题排查变异表、隐式游标和触发体事务控制的那些坑5.1 现象 ORA-04091表在触发时发生了变化在行级触发器里尝试查询触发器自己所在的表经常会碰到“表发生了变化触发器/函数不能读它”的错误。Oracle 的约束是同一张表上正在被修改的行在行级触发器中不能再执行 SELECT、UPDATE 等操作否则会读到不一致的数据状态。最常见的是在 AFTER INSERT OR UPDATE 触发器里做了一个 SELECT COUNT(*) FROM teachers 的统计操作直接触发这个错。解决思路是把这种统计需求外包出去。典型做法是写一个自治事务的存储过程由触发器调用它去完成对原表的读取或者把这个统计逻辑彻底从触发器中移出去改由语句级触发器的 AFTER 事件配合物化视图来实现。注意自治事务要在存储过程里声明 PRAGMA AUTONOMOUS_TRANSACTION单纯在触发器里包一层 BEGIN END 没有用。5.2 现象 触发器执行了但影响行数为零隐式游标 SQL%ROWCOUNT 的错觉有人在行级触发器里写了隐式游标即 SELECT INTO、UPDATE、DELETE 这类 SQL希望通过 SQL%ROWCOUNT 判断这一次操作有没有真正修改到数据。实际情况是在触发器里执行 SELECT INTO一条语句只能返回一行SELECT 成功时 SQL%ROWCOUNT 的值无法像普通 DML 那样直观反映行数。遇到这种需求我的做法是如果需要知道影响行数就在触发体里写显式游标自己 FETCH 自己判读如果只是想判断某个条件是否存在用 SELECT COUNT(*) INTO 一个变量也行但别依赖 SQL%ROWCOUNT。另外要确认一个事实——SQL%ROWCOUNT 在触发器里会被后续的执行语句覆盖所以临时保存在局部变量里是关键操作。5.3 现象 触发器不生效ALTER TRIGGER DISABLE 之后的残留问题排除掉编译错误之后触发器无声无息不工作第一个怀疑对象就是触发器处于 DISABLED 状态。Oracle 里 ALTER TRIGGER 触发器名 DISABLE 能停用一个触发器这种情况在开发环境里很常见。但真正隐蔽的是如果你在创建时用了 DISABLE 关键字或者触发器被 DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY 这种高级特性干扰过它的状态你可能根本不知道。检查状态有两条路一条是用 PL/SQL Developer 打开触发器列表看状态图标另一条是查数据字典后面会给出具体查询语句。别忘了还有一个隐藏开关叫 ALTER TABLE teachers DISABLE ALL TRIGGERS这个命令会把表上所有触发器全部停用而且不会留下明显的告警日志排查时很容易忽略。5.4 现象 触发器内事务控制失效COMMIT 不能随便写很多半路转 Oracle 的开发者是把 SQL Server 的写触发器的习惯带了过来在触发体里顺手写个 COMMIT。在 Oracle 里这直接就是 ORA-04092 错误不能在触发器中提交。Oracle 的事务模型以会话为单位触发器和触发它的 DML 语句共享同一个事务事务的终结只能由发起方控制。如果确实需要在触发器里独立提交一段逻辑——比如记录日志不让日志回滚影响业务——方案是自治事务。把日志写入封装成一个 W 存储过程内部声明 PRAGMA AUTONOMOUS_TRANSACTION然后触发器里调用它。这个做法在实际项目里几乎是标准配置但它会让调试难度上一个台阶因为自治事务里修改的数据对主事务不可见反过来也一样。6. 开发与调试触发器用数据字典做版本管理三分钟定位问题触发器开发触发器不能只靠”能跑就行“。生产环境里我有一套固定的检查流程这里分享其中最核心的三个动作。第一件事确认触发器状态。下面这条语句可以列出指定表上的所有触发器及其状态SELECT trigger_name, status, trigger_type, triggering_event FROM user_triggers WHERE table_name TEACHERS;STATUS 字段的值是 ENABLED 或 DISABLED看到 DISABLED 先别急着启用搞清楚是谁、什么时候禁用它。TRIGGER_TYPE 可以区分 BEFORE 还是 AFTER、行级还是语句级。第二件事没有 DDL 脚本时用 DBMS_METADATA 从数据字典直接导出触发器的完整源码。这个功能我印象非常深——有一次客户的生产库触发器失效因为运维环境变更导致源文件全部丢失最后就是用这段脚本把创建语句完整拉回来的所以把这条用法写进自己的代码库相当于买了一份后悔药SELECT DBMS_METADATA.GET_DDL(TRIGGER, MY_TRIGGER) FROM DUAL;这个函数会把触发器的完整 CREATE OR REPLACE TRIGGER 文本返回出来可以原样保存成 .sql 文件作为版本管理的基线。两个注意点触发器名要大写写全称如果触发器属于某个包管理查询出来的文本可能需要手动调整格式才能直接执行。第三件事验证触发器的实际执行路径。生产上不能用 DBMS_OUTPUT 打印日志因为没有人会盯着控制台看输出。常用做法是在触发体里加一段安全的日志写入关键值全部记下来CREATE OR REPLACE TRIGGER my_trigger1 AFTER INSERT OR UPDATE OR DELETE ON teachers FOR EACH ROW DECLARE v_info CHAR(10); BEGIN IF inserting THEN v_info : INSERT; ELSIF updating THEN v_info : UPDATE; ELSE v_info : DELETE; END IF; INSERT INTO sql_info(info) VALUES (v_info); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END my_trigger1; /等一下刚才专门说过触发器里不能写 COMMIT这里为什么又有因为如果这段代码在普通触发器里执行到 COMMIT 会直接报 ORA-04092。上面这种写法只有在 sql_info 写入封装成自治事务存储过程并且由触发器调用时才成立。我在排查日志缺失问题时经常见到同事把 COMMIT 直接放到触发器里一旦报错又用 WHEN OTHERS THEN NULL 把异常吞掉导致日志表和业务数据双双回滚——这几乎是最隐蔽的触发器事故组合。所以我在代码里会用 ALTER TRIGGER 触发器名 DISABLE 和 ENABLE 来隔离问题而不是靠 COMMIT 或异常捕获去修补逻辑。碰到触发器怎么都不触发的情况先查 USER_TRIGGERS 的状态再查表级触发器开关最后再看代码逻辑。从那以后我每次交付触发器代码都会强制走一遍三步流程创建语句保存进版本库、数据字典里的状态和源文本做一次快照、测试用例至少要覆盖正常插入和重复插入两条路径。这套流程帮我避开了不少线上的审计断档和触发器静默失效希望也能帮到你。本文还有配套的精品资源点击获取