在Oracle中使用触发器

 

一． 触发器
触发器是一个命名的程序单元，用来回应数据库中所发生的事件。根据发生事件的不同，触发器分为DML Triggers, DDL Triggers, Database Event Triggers, INSTEAD OF Triggers, AFTER SUSPEND Triggers, Maintaining Triggers .

二． 功能
1. 对表作修改时进行验证
2. 数据库维护自动化
3. 对数据库的管理操作进行细粒度的控制

三． 功能举例
1. 允许/限制对表的修改
2. 自动生成派生列，比如自增字段
3. 强制数据一致性
4. 提供审计和日志记录
5. 防止无效的事务处理
6. 启用复杂的业务逻辑

四． 使用触发器
DML触发器是由数据操纵语言触发的，如INSERT, UPDATE, DELETE等，是开发人员较常用的，其它触发器一般都只有DBA使用。

DML 触发器的格式：

CREATE [OR REPLACE] TRIGGER trigger name
{BEFORE AFTER}
{INSERT DELETE UPDATE UPDATE OF column list} ON table name
[FOR EACH ROW]
[WHEN (...)]
[DECLARE ... ]
BEGIN
... executable statements ...
[EXCEPTION ... ]
END [trigger name];

DML触发可以发生在对应语句执行前或执行后，有两个级别，行级别和语句级别，行级别是指每改变一行数据就触发一次。在使用行级别时，可以调用系统中的两个变量:NEW和:OLD两个绑定变量（非正规的PL/SQL的变量），:NEW可以表示刚插入表中的行，用:NEW.加上相应的字段名可以取到相应的值，因此DELETE语句触发后，:NEW中是没有值的。

         CREATE OR REPLACE TRIGGER validate_employee_changes AFTER INSERT OR UPDATE ON employee FOR EACH ROW BEGIN check_age (:NEW.date_of_birth); check_resume (:NEW.resume); END;

这段代码就是行级别的，语句级别的写法也是一样，只要去掉FOR EACH ROW。

使用说明：

For each row的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的话，表示是表级触发器，则无论操作多少行，都只触发一次；

When条件的出现说明了，在DML操作的时候也许一定会触发触发器，但是触发器不一定会做实际的工作，比如when 后的条件不为真的时候，触发器只是简单地跳过了PL/SQL块；

五． 问题
      在写一个修改新插入行的触发器时发现一些问题，在这里简单列一下：
       create or replace trigger bulletin_num after insert on ems_bulletin for each row declare v_serId VARCHAR2(14); begin P_GetSerialNo (2,v_serId); ----生成流水号，放到变量v_serId中,第一个参数就是给其设置的类型了 update ems_bulletin set ems_bulletin.bulletin_num = v_serId where ems_bulletin.id = :NEW.id; end bulletin_num;

      这是最初的代码，P_GetSerialNo (2,v_serId)是一个存储过程，可以产生一个流水号赋给v_serId，之后会更新刚插入的行的一个字段为该值。编译可以通过，插入数据时出错，“表 ANNOUNCE.EMS_BULLETIN 发生了变化，触发器/函数不能读。”修改代码如下，可解决该问题。

create or replace trigger bulletin_num after insert on ems_bulletin for each row declare v_serId VARCHAR2(14); pragma autonomous_transaction; begin P_GetSerialNo (2,v_serId); ----生成流水号，放到变量v_serId中,第一个参数就是给其设置的类型了 update ems_bulletin set ems_bulletin.bulletin_num = v_serId where ems_bulletin.id =:NEW.id; commit; end bulletin_num;
这里在触发器中使用了一个自治事务，此时插入数据不会报错，但由于把插入操作和更新放到了一个事务中，使得执行update时前面的操作没有提交，在数据库中还找不到刚插入的行，导致触发器无效。这样就不能用自治事务，只好将触发器改为语句级别，同时:NEW也不可以用了。代码就变成：

create or replace trigger bulletin_num after insert on ems_bulletin declare v_serId VARCHAR2(14); begin P_GetSerialNo (2,v_serId); ----生成流水号，放到变量v_serId中,第一个参数就是给其设置的类型了 update ems_bulletin set ems_bulletin.bulletin_num = v_serId where ems_bulletin.id = (select max(id) from ems_bulletin); end bulletin_num;
最终的代码就是如此，不过P_GetSerialNo (2,v_serId)中还有一个commit语句，由于触发器中不能包含commit语句，触发器调用的存储过程也不能包含commit，所以还要把这个地方改掉。这样，这个触发器的使用就正常了。

六． 补充
代码修改为：

create or replace trigger bulletin_num before insert on ems_bulletin for each row declare v_serId VARCHAR2(14); pragma autonomous_transaction; begin P_GetSerialNo (2,v_serId); ----生成流水号，放到变量v_serId中,第一个参数就是给其设置的类型了 :NEW.bulletin_num := v_serId; commit; end bulletin_num;
由于这里开启了一个事务，所以P_GetSerialNo (2,v_serId)中包含commit语句也不会报错。

关键字：

:NEW 和:OLD使用方法和意义，new 只出现在insert和update时，old只出现在update和delete时。在insert时new表示新插入的行数据，update时new表示要替换的新数据、old表示要被更改的原来的数据行，delete时old表示要被删除的数据。