ORACLE触发器概述之【行触发器】【weber出品】

1. 行触发器的定义

行触发器是指执行DML操作时，每作用一行就触发一次的触发器。审计数据变化时，可以使用行触发器

2. 建立行触发器的语法如下：

create or replace trigger trigger_name
timing event1 [or event2 event3]
on table_name
(referencing old as old | new as new)
for each row
(when condition)
pl/sql block;

trigger_name:用于指定触发器名

timing：用于指定触发时机（before或after）

event：用于指定触发事件（INSERT,UPDATE,DELETE）

referencing：用于指定引用新旧数据的方式，使用old修饰符引用旧数据，使用new修饰符引用新数据

table_name:用于指定DML操作所对应的表

for each row：表示建立行触发器

when：when子句是可选的，用于指定触发条件

 

3. 行触发器的分类

3.1 BEFORE触发器

在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制但某些情况下使用约束可能无法实现复杂的商业逻辑或企业规则

此时可以考虑使用before行触发器

下面以确保雇员工资不能低于其原有工资为例，说明建立before行触发器的方法，示例如下：

create or replace trigger tr_emp_sal
  before update of sal on emp
  for each row
begin
  if :new.sal < :old.sal then
    raise_application_error(-20001, '工资只涨不降');
  end if;
end;

执行结果：

SQL> update emp set sal=700 where ename='ywb';
update emp set sal=700 where ename='ywb'
ORA-20111: 工资只涨不降
ORA-06512: 在 "SCOTT.TR_EMP_SAL", line 3
ORA-04088: 触发器 'SCOTT.TR_EMP_SAL' 执行过程中出错

 

3.2 建立after行触发器

为了审计DML操作，可以使用语句触发器或oracle系统提供的审计功能，而为了审计数据变化，则应该使用after行触发器，下面以审计雇员工资变化为例，说明使用after行触发器的方法，在建立触发器之前，首先应建立存放审计数据的表audit_emp_change

create table audit_emp_change(name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);

为了审计所有员工的工资变化和员工工资的更新日期，必须建立after行触发器

create or replace trigger tr_audit_sal
  after update of sal on emp
  for each row
declare
  v_temp int;
begin
  select count(*)
    into v_temp
    from audit_emp_change
   where name = :old.ename;
  if v_temp = 0 then
    insert into audit_emp_change
    values
      (:old.ename, :old.sal, :new.sal, sysdate);
  else
    update audit_emp_change
       set oldsal = :old.sal, newsal = :new.sal, time = sysdate;
  end if;
end;

执行结果：

SQL> select ename,sal from emp;
ENAME            SAL
---------- ---------
ywb           800.00
SMITH         800.00
ALLEN        1600.00
WARD         1250.00
JONES        2975.00
MARTIN       1250.00
BLAKE        2850.00
CLARK        2450.00
SCOTT        1200.00
KING         5000.00
TURNER       1500.00
ADAMS        1100.00
JAMES         950.00
FORD         3000.00
MILLER       1300.00
15 rows selected

SQL> update emp set sal=sal+100 where ename='ywb';
1 row updated

SQL> select * from audit_emp_change;
NAME         OLDSAL   NEWSAL TIME
---------- -------- -------- -----------
ywb          800.00   900.00 9/3/2014 11

SQL> update emp set sal=sal+100 where ename='YWB';
0 rows updated

SQL> update emp set sal=sal+100 where ename='ywb';
1 row updated

SQL> select * from audit_emp_change;
NAME         OLDSAL   NEWSAL TIME
---------- -------- -------- -----------
ywb          900.00  1000.00 9/3/2014 11