在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。

五． 问题
在写一个修改新插入行的触发器时发现一些问题，在这里简单列一下：

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语句也不会报错。