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

一、触发器概述

与表，视图，模式，或者数据库相关的PL/SQL过程，当触发条件被触发时，自动执行

分类：

1.语句触发器

2.行触发器

二、语句触发器

1. 什么是语句触发器

语句触发器，是指当执行DML语句时被隐含执行的触发器，如果在表上针对某种DML操作建立了语句触发器，那么当执行DML操作时会自动执行触发器的相应代码

当审计DML操作或者确保DML操作安全执行时，可以使用语句触发器，使用语句触发器时，不能记录列数据的变化

2. 语法：

create [or replace] trigger trigger_name
timing event1[or event2 or event3]
on table_name
pl/sql block;

trigger_name:用于指定触发器名
timing：指定触发时机（before或after）
event：指定触发事件（INSERT,UPDATE,和DELETE）；
table_name:用于指定DML操作对应的表名

3. 语句触发器的分类

3.1 BEFORE触发器

为了确保DML操作在正常情况下执行，可以基于DML操作建立BEFORE语句触发器，例如为了禁止工作人员在休息日改变雇员信息，开发人员可以建立BEFORE语句触发器，以实现数据的安全保护

示例如下：

create or replace trigger tr_sec_emp
  before insert or update or delete on emp
begin
  if to_char(sysdate, 'DY', 'nls_date_language = AMERICAN') in
     ('SAT', 'SUN') then
    raise_application_error(-20001, '不能在工作日修改员工信息');
  end if;
end;

　　　　修改系统日期：

[root@oracle oracle]# 
[root@oracle oracle]# date 
Wed Sep  3 22:37:27 CST 2014
[root@oracle oracle]# date -s "Wed Sep  6 22:37:27 CST 2014"
Sat Sep  6 22:37:27 CST 2014
[root@oracle oracle]# date
Sat Sep  6 22:37:28 CST 2014

提示：

SQL>update emp set sal=sal+100 where empno=7788;

ORA-20001:不能在工作日修改员工信息

3.2 使用条件谓词：

     当在触发器中同时包含多个触发条件（INSERT,UPDATE,DELETE）时，为了在触发器源代码中区分具体

的触发条件，可以使用三个条件谓词，可以使用以下三个条件谓词：

INSERTING：当触发事件是insert操作时，该条件谓词返回值为TRUE，否则为FALSE
UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则为FALSE
DELETING：当触发事件是DELETE作时，该条件谓词返回值为TRUE，否则为FALSE

create or replace trigger tr_sec_emp
  before insert or update or delete on emp
begin
  if to_char(sysdate, 'DY', 'nls_date_language = AMERICAN') in
     ('SAT', 'SUN') then
    case
      when inserting then
        raise_application_error(-20001, '不能在工作日增加员工');
      when updating then
        raise_application_error(-20001, '不能在工作日修改员工');
      when deleting then
        raise_application_error(-20001, '不能在工作日解雇员工');
    end case;
  end if;
end;

执行过程：

SQL> update emp set sal=sal+100 where empno=7788;
update emp set sal=sal+100 where empno=7788
ORA-20001: 不能在非工作室更新员工
ORA-06512: 在 "SCOTT.TR_SEC_EMP", line 8
ORA-04088: 触发器 'SCOTT.TR_SEC_EMP' 执行过程中出错

3.3 建立after语句触发器：

为了审计DML操作或者DML操作之后执行汇总运算，可以使用after触发器例如，为了审计在emp表上insert，update，delete操作次数，可以建立after触发器

在建立after触发器之前，首先建立审计表audit_table
创建审计表：

create table audit_table(name varchar2(20),ins int,upd int,del int,
starttime date,endtime date);

创建触发器：

create or replace trigger tr_audit_table
  after insert or delete or update on emp
declare
  v_temp int;
begin
  select count(*) into v_temp from audit_table where name = 'EMP';
  if v_temp = 0 then
    insert into audit_table values ('EMP', 0, 0, 0, SYSDATE, null);
  end if;
  case
    when inserting then
      update audit_table set ins = ins + 1 where name = 'EMP';
    when deleting then
      update audit_table set del = del + 1 where name = 'EMP';
    when updating then
      update audit_table set upd = upd + 1 where name = 'EMP';
  end case;
end;

执行结果：

SQL> update emp set sal=sal+100 where empno=7788;
1 row updated

SQL> select * from audit_table;
NAME       INS UPD DEL STARTTIME   ENDTIME
---------- --- --- --- ----------- -----------
EMP          0   1   0 9/3/2014 10