PL/SQL触发器怎么编写 PL/SQL触发器递归触发怎么排查

PL/SQL触发器的编写，和触发器递归触发的排查，这两件事情的关键，是需要先弄清楚触发器到底是在什么时候执行、针对哪一张表来执行、它是执行一次，还是每一行都会执行一次。在Oracle当中，trigger是存储在数据库里面的一种PL/SQL单元，它会在被指定的数据库事件发生的时候，自动地被触发并执行。触发器如果写得好，可以用它来做审计、补充一些字段，或者是进行数据的校验；可要是写得太重了，就容易带来递归触发、性能下降，还有维护起来比较困难这一类的问题。

一、PL/SQL触发器怎么编写

PL/SQL触发器，一般是由触发时机、触发事件、触发的对象，还有执行逻辑，这么几个部分组成的。在动手写之前，需要先判断一下，这一套逻辑是不是真的适合被放在触发器里面，不要把那些普通的业务流程，全都一股脑地塞进数据库的触发器里面去。

1、先把触发的时机和事件确定下来

要围绕着【BEFORE】、【AFTER】、【INSERT】、【UPDATE】还有【DELETE】，来确定触发器执行的条件。

比如说，在新增加一个员工的时候，需要自动地写入创建的时间，那就可以使用BEFORE INSERT这种形式；在修改了订单的状态以后，需要记录一条审计的日志，那就可以使用AFTER UPDATE这种形式。BEFORE这一类，要更加适合在数据被存进数据库以前，去补充字段，或者是做一下校验；AFTER这一类，则更加适合在数据发生了变动以后，去写日志，或者是去同步一些辅助的表。

2、把行级触发器和语句级触发器区分清楚

如果需要对每一行的数据，都进行处理，那就去写上FOR EACH ROW这个子句；如果只是想在一条SQL被执行的时候，触发那么一次，那就不要去写这个子句。Oracle对于FOR EACH ROW的说明是这样的：行级的触发器，会对触发语句所影响到的每一行，都触发一次；如果把它给省略掉了，通常它就是语句级别的触发器。比如说，一次UPDATE语句更新了一百行，那么行级的触发器，就会被执行一百次，而语句级的触发器，只会被执行一次。这个区别是很重要的，有不少性能上的问题，还有递归的问题，都是因为本来只需要做一次处理，却被写成了行级的触发器。

3、要正确地使用OLD和NEW这两个值

在行级的触发器里面，可以通过:OLD和:NEW去访问发生变化前后的字段值。在做新增操作的时候，通常是去看:NEW；在做删除操作的时候，通常是去看:OLD；在做更新操作的时候，这两者都有可能会被用到。有一个比较常见的写法是这样的：

这一类触发器的逻辑，要尽量写得简短一些，不要在它的里面去写那些复杂的循环、远程的调用，或者是大范围的查询。触发器它是跟着DML操作一起执行的，如果触发器里面卡住了，那么前面的插入、更新或者是删除操作，也同样会受到影响。

二、PL/SQL触发器递归触发怎么排查

触发器出现了递归触发，比较常见的表现，是SQL执行起来非常慢、会报错、调用堆栈很深，又或者是一条更新操作，会带出来一连串的更新。这种情况，倒不一定就是触发器“自己调用自己”，它有可能是A表的触发器，去更新了B表，而B表的触发器，又反过来更新了A表。

1、先去查一下触发器彼此之间的调用链

要对照【USER_TRIGGERS】、【触发器的代码】和【被修改的表名】，去找出每一个触发器里面，到底执行了哪些INSERT、UPDATE、DELETE操作。如果某一个触发器，它操作了另外一张表，而那张表上面，也同样存在着触发器，那就需要继续往下排查。

Oracle把这种，一个触发器导致另一个触发器继续被触发的情况，称为cascading triggers，并且允许最多有三十二个触发器同时发生级联。当看到有多层的表，在互相更新的时候，就需要特别小心了。

2、去检查一下，是不是修改了当前的表

在行级的触发器里面，如果去查询，或者是修改了那条正在被触发语句所修改的同一张表，就比较容易碰到mutating table这个问题。Oracle对于mutating-table restriction的说明是，它会阻止触发器，去查询或者修改当前那条触发语句，正在修改的表；发生这种情况的时候，就有可能会出现ORA-04091这个错误。比如说，在emp这张表的行级更新触发器里面，又去查询，或者是更新emp表本身，这就很容易出问题。遇到这种逻辑，就需要去考虑把它改成语句级别的处理，使用复合触发器，或者是先把数据暂存起来，然后再去做统一的处理。

3、看一看触发的条件，是不是过于宽泛了

有些触发器，被写成了AFTER UPDATE ON表名这种形式，但是没有去限制触发所针对的字段。这样一来，只要这张表上面，有任何一个字段被更新了，都会把那个逻辑给触发起来。要是触发器内部，又去更新了同一张表的另外一个字段，那么就有可能会反反复复地去触发。一个更加稳当的写法，是使用UPDATE OF字段名，去把触发的范围给缩小，再或者，是在触发器里面，去判断一下:OLD和:NEW，看看目标列是不是真的发生了变化。

三、递归触发问题怎么调整

在排查出了递归的链路以后，不要只是靠着去禁用触发器，来把问题解决掉。禁用触发器，只能起到临时止血的作用，真正需要去处理的，是逻辑的边界，还有触发的条件。

1、把复杂的逻辑，从触发器里面移出来

如果触发器里面，包含了大量的业务判断、跨表的更新，还有状态的流转，那就可以去考虑，把它改成存储过程，或者是业务服务那边的调用来实现。触发器它更适合去承担那些轻量的、明确的、靠近数据层的动作，并不太适合去承载完整的业务流程。

2、增加防止重复触发的条件

要对照【触发字段】和【状态字段】，去增加一些判断，只有在目标字段，真正发生了变化的时候，才去执行更新。

比如说，订单的状态，要从A变成了B，才去写一条日志，如果状态并没有发生变化，那就不要重复地去写日志。这样做，是可以减少那些没有意义的触发，也能够降低递归链路，继续往外面扩散的概率。

3、把它调整为语句级别，或者是复合触发器

要是问题，是来自于行级触发器逐行去处理，那就可以去考虑把它改成语句级别的触发器，或者是使用compound trigger，分阶段地去进行处理。这背后的思路就是，在行级阶段，只去收集那些必要的信息，等到语句结束了以后，再去做统一的更新，这样就能避免在每一行的处理当中，反反复复地去访问当前的表。

总结

PL/SQL的触发器应当怎样去编写，还有PL/SQL触发器发生了递归触发，又该怎样去进行排查，这两者的关键，是先把触发的时机、触发的事件，还有触发的粒度，给选对了。行级的触发器，比较适合用来逐行去补值，或者是做校验；语句级的触发器，则更加适合用来做一次性的处理。在排查递归触发的时候，需要去看一看触发器之间的DML调用链、是不是修改了当前的表、触发的条件，是不是过于宽泛了。在做调整的时候，要尽量去缩小触发的范围，把那些复杂的业务逻辑，从触发器里面给移出来，并且使用语句级别的触发器，或者是复合触发器，去减少反反复复触发的情况。