oracle触发器详解

简单介绍数据库触发器
（1）数据库触发器是一种PL/SQL命名块，是数据库中一种较为复杂的用来强制业务规则、数据完整性和一致性的机制，它存放在数据库中，在特定的事件发生时，可以自动的被数据库执行。
（2）数据库触发器的用处
当表被修改时执行校验；
自动维护数据库；
用一种细粒度的方式将规则应用于可接受的数据库管理活动
最常用的是DML触发器，用于执行校验，设置和初始值，审核改变，甚至禁止某种DML操作
（3）触发器的分类
BEFORE触发器
AFTER触发器
语句级触发器
行级触发器

 第一章：DDL触发器概述

通过DDL触发器可以记录特定的DDL操作。DDL触发器是从ORACLE 8i开始具有的触发器类型。在ORACLE 9i中，DDL触发器得到了加强。DDL触发器的触发事件包括：

l         BEFORE / AFTER ALTER

l         BEFORE / AFTER CREATE

l         BEFORE / AFTER DROP

l         BEFORE / AFTER RENAME

l         BEFORE / AFTER ANALYZE

l         BEFORE / AFTER ASSOCIATE STATISTICS

l         BEFORE / AFTER DISASSOCIATE STATISTICS

l         BEFORE / AFTER AUDIT

l         BEFORE / AFTER NOAUDIT

l         BEFORE / AFTER COMMENT

l         BEFORE / AFTER DDL

l         BEFORE / AFTER GRANT

l         BEFORE / AFTER REVOKE

l         BEFORE / AFTER TRUNCATE

l         AFTER SUSPEND

要创建DDL触发器，需要一定的系统权限，这些权限包括：

l         create trigger

l         create any trigger

l         administer database trigger

l         alter any trigger

l         drop any trigger

DDL触发器可以用于细化的DDL操作审计。一般的做法是设计一张日志表，当DDL触发器激活后，通过系统相关的系统事件函数获取锁需要的信息，然后把数据插入日志表中。这样，就可以记录系统中需要审计的DDL操作。DDL触发器可以针对Database级或者SCHEMA级���

要注意的是，DDL触发器仅对以下的对象有效：cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, user。

例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

创建一个表的空表：

create  table emp_log as  select * from emp   where 1=2;(将删除记录写到职工删除日志中)

又为emp_log添加三个字段who、action、time

alter table emp_log add action varchar2(20);

 alter table emp_log add time date;

alter table emp_log add who varchar2(20);

create or replace trigger tr_del_emp

--BEFORE触发器
  before delete
  on emp
  for each row
  begin
      INSERT INTO emp_log(who,action,time,deptno , empno, ename , job ,mgr , sal , comm , hiredate )
           VALUES(user,'delete',sysdate, :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
  end;
测试该触发器：

delete from emp where empno=7369;

select * from emp_log;

因此同理可以写intsert的触发器。

update的情况太多。

例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

create or replace trigger tr_dept_time
before insert or delete or update
on dept
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or to_char(sysdate,'hh24:mi') not between '08:30' and '18:00'
  then raise_application_error(-20000,'不是上班时间，不能修改dept表');
  end if;
end;

to_char(sysdate,'day')日期转换

 to_char(sysdate,'hh24:mi') not between '08:30' and '18:00'（时间转换）

raise_application_error(-20000,'不是上班时间，不能修改dept表');

show error;

 RAISE_APPLICATION_ERROR :是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上SQLPLU或者其他前台开发语言)

raise_application_error(异常类型,传递信息)
异常类型：number() 值域：-20000 到-20999
传递信息：varchar2（2000）

dbms_STANDARD包的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。

语法如下

RAISE_APPLICATION_ERROR(errorNumber,errorString)
errorNumber是数值在-20000到-20999之间，errorString为自定义的错误信息。
如：
  if to_char(sysdate,'day') in ('星期六','星期日') or to_char(sysdate,'hh24:mi') not between '08:30' and '18:00'
  then raise_application_error(-20000,'不是上班时间，不能修改dept表');
  end if;
当在sqlpus中测试时，一旦没有要更新的行，则抛出这样的异常：

例3：限定只对部门号为80的记录进行行触发器操作。

对某个字段进行限制。

create or replace trigger tri_deptname
before insert
on dept
for each row
begin

（old，new的妙用还真好）

--:old.deptno
  if :new.deptno=30
  then
  raise_application_error(-20000,'deptno 不能为30！');
  end if;
end;
测试语句：insert into emp (empno)values(30);

create or replace trigger tri_name
    before
    insert or update or delete on emp
    for each row
    begin
     if inserting then
        dbms_output.put_line('添加dml');
     end if;
       if updating then
        dbms_output.put_line('修改dml');
     end if;
       if deleting then
        dbms_output.put_line('删除dml');
     end if;
    end;
   
    delete from emp where empno =7369;
    update emp set empno =1 where empno = 7369;

(只是在测试的时候，只是不能进行的操作)

--总体的需求：创建一张表 写触发器实现逐渐的自动增长
create table t_temp_chapter8_tri(
pk_id number(8),
f_name varchar2(50)
) 

创建一个索引：

create sequence seq_chapter8_temp increment by 1 start with 1;

--创建触发器
create or replace trigger tri_temp
before insert
on t_temp_chapter8_tri
for each row
begin
     select seq_chapter8_temp.nextval into :new.pk_id from dual;
end;

创建序列后，每次新增时用
序列名.nextval 就可以自增了

测试语句：

             --添加数据
            insert into t_temp_chapter8_tri(f_name) values('test');

            insert into t_temp_chapter8_tri(f_name) values('test');

查看表中的数据：

SQL> select * from t_temp_chapter8_tri;
 
    PK_ID F_NAME
--------- --------------------------------------------------
        1 test
        2 test
        3 test

需求：

  -- 对emp 表添加一个触发器功能 ：
    -- 在修改员工 工资的时候工资不能低于之前的工资
    create or replace trigger tri_updae_empsal
    before
    update of sal on emp
    for each row
    begin
      -- 获得原始数据和将要修改的数据比较
     if :old.sal> :new.sal then
     -- 抛出窗口异常， 阻止执行DML执行
     raise_application_error(-20000,'工资不能低');
     end if;
    end;

测试语句：update emp set sal=11 where empno=7499;

http://blog.csdn.net/indexman/article/details/8023740/

--创建一张学生表
    --字段包括 主键、学生名字，学生年龄，学生性别，学生身份证号码。
create table t_student(
 t_no  number(3) primary key,
 t_name varchar2(40),
 t_age  number(3),
 t_gender varchar2(4),
 t_idcard varchar2(20)
);
 --1、写一个触发器实现主键列数据自动增长
 create sequence seq_student_id increment by 1 start with 1;
 --创建触发器
 create or replace trigger tri_t_student_id_autoadd
 before
 insert on t_student
 for each row
 begin
  select seq_student_id.nextval into :new.t_no from dual;

--创建的序列seq_student_id(名字)

--为seq_student_id设置了一些属性，将这个序列每一次的增值给主键t_no（字段）

--以达到t_no主键每一次添加后自动加一的效果。
 end;
 --测试 
 insert into t_student(t_no) values(99);
 select * from t_student;

 2、写一个触发器实现数据添加时 验证学生的年龄要求大于18，否则不能添加
 create or replace trigger tri_t_student_age18
 before
 insert on t_student
 for each row
 begin
   if :new.t_age<=18 then 
     raise_application_error(-20091,'年龄必须大于18岁！');
   end if;
 end;  
 --测试
 insert into t_student(t_age) values(23);
 select * from t_student;
 3、写一个触发器，实现验证学生的性别为男或者是女。否则不能添加
 create or replace trigger tri_t_student_gender
 before
 insert on t_student
 for each row
 begin
   if :new.t_gender in('男','女') then 
     null;
   else
     raise_application_error(-20092,'性别为男或者是女！');
   end if;
 end;  
 insert into t_student(t_gender) values('男');
 select * from t_student;
 4、写一个触发器，实现验证学生的身份证号码的长度要求为18。否则不能添加
 create or replace trigger tri_t_student_idcard
 before
 insert on t_student
 for each row
 begin

--如果对的是什么都不做的
   if lengthb(:new.t_idcard)=18  then 
     null;
   else
     raise_application_error(-20093,'身份证号码的长度要求为18！');
   end if;
 end;  
 insert into t_student(t_idcard,t_gender) values('123456789012345678','女');
 select * from t_student;
 
 alter table t_student enable all triggers;--启用触发器
 alter table t_student disable all triggers;--禁用触发器
 alter table 触发器名 complie--重新编译触发器

重新编译触发器
如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。
在PL/SQL程序中可以调用ALTER TRIGGER语句重新编译已经创建的触发器，格式为：           
ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]
       其中：DEBUG 选项要器编译器生成PL/SQL 程序条使其所使用的调试代码。

 例5：在触发器中调用过程。

这个过程插入一条数据在
 
CREATE OR REPLACE PROCEDURE add_job_history
 ( p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
  , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
 INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;


--创建触发器调用存储过程...
CREATE OR REPLACE TRIGGER update_job_history
 AFTER UPDATE OF job_id, department_id ON employees
 FOR EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;

第二章创建替代(INSTEAD OF)触发器
 
创建触发器的一般语法是:
 
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;
 
其中：
           INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。
           FOR EACH ROW选项说明触发器为行触发器。

行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。
           REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。
 
    INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：

例1：
 
CREATE OR REPLACE VIEW emp_view AS 
SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
FROM emp GROUP BY deptno;

问题：
 
在此视图中直接删除是非法：
delete from
emp_view
where deptno=10
 
ORA-01732: 此视图的数据操纵操作非法

解决办法：
 
但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，

即删除EMP表中所有基准行：
 
CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD OF DELETE 
ON emp_view 
FOR EACH ROW
BEGIN
   DELETE FROM emp 
   WHERE deptno= :old.deptno;
END emp_view_delete; CREATE OR REPLACE TRIGGER emp_view_delete;

视图的改变是随着基表的改变而改变的，在修改视图的时候，应该改变基表。

(不用INSTEAD OF DELETE 能不行吗)

CREATE OR REPLACE TRIGGER emp_view_delete
before DELETE 
ON emp_view 
FOR EACH ROW
BEGIN
   DELETE FROM emp 
   WHERE deptno= :old.deptno;
END emp_view_delete;
 
ORA-25001: 无法在视图上创建此类型的触发器

oracle简明教程上的一个例子：

首先基于student表创建视图student_view,该视图检索student表中的所用数据，但将student表中的sage列+1。视图创建视图，

create view student_view

as

select sid,sname,sage+1 new_age

from student

with check option;

视图已创建。

分析：由于student_view视图中的new_age列队student表中的sage列记性了数学计算，所以不能之间对该列进行dml操作。

insert into student_view values(1004,'petter',22);

出现错误

解决的方案：

如果想通过student_view视图向student表添加记录，则需要使用instead of insert触发器。触发器创建如下：

create trigger insteadof_stucent_view

instead of insert

for each row

begin

       insert into student(sid,sname,sage)

      values(:new,sid,:new.sname,:new.new_age)

 end insteadof_student_view; 

创建INSTEAD OF触发器需要注意以下几点：

l         只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

l         不能指定BEFORE 或 AFTER选项。

l         FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。

l         没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了

第三章 创建系统事件触发器

    ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。

创建系统触发器的语法如下： 

 
CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER} 
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;
 
其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
        database_event_list：一个或多个数据库事件，事件间用 OR 分开；

系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。

当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。

当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

下面给出系统触发器的种类和事件出现的时机（前或后）：

事件

允许的时机

说明

STARTUP

AFTER

启动数据库实例之后触发

SHUTDOWN

BEFORE

关闭数据库实例之前触发（非正常关闭不触发）

SERVERERROR

AFTER

数据库服务器发生错误之后触发

LOGON

AFTER

成功登录连接到数据库后触发

LOGOFF

BEFORE

开始断开数据库连接之前触发

CREATE

BEFORE，AFTER

在执行CREATE语句创建数据库对象之前、之后触发

DROP

BEFORE，AFTER

在执行DROP语句删除数据库对象之前、之后触发

ALTER

BEFORE，AFTER

在执行ALTER语句更新数据库对象之前、之后触发

DDL

BEFORE，AFTER

在执行大多数DDL语句之前、之后触发

GRANT

BEFORE，AFTER

执行GRANT语句授予权限之前、之后触发

REVOKE

BEFORE，AFTER

执行REVOKE语句收权限之前、之后触犯发

RENAME

BEFORE，AFTER

执行RENAME语句更改数据库对象名称之前、之后触犯发

AUDIT / NOAUDIT

BEFORE，AFTER

执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

说明：创建系统时间触发器需要使用on database子句，即表示创建的触发器是数据库级触发器。穿件系统事件触发器需要用户具有dba权限。

1.5.2 DDL触发器概述

通过DDL触发器可以记录特定的DDL操作。DDL触发器是从ORACLE 8i开始具有的触发器类型。在ORACLE 9i中，DDL触发器得到了加强。DDL触发器的触发事件���括：

l         BEFORE / AFTER ALTER

l         BEFORE / AFTER CREATE

l         BEFORE / AFTER DROP

l         BEFORE / AFTER RENAME

l         BEFORE / AFTER ANALYZE

l         BEFORE / AFTER ASSOCIATE STATISTICS

l         BEFORE / AFTER DISASSOCIATE STATISTICS

l         BEFORE / AFTER AUDIT

l         BEFORE / AFTER NOAUDIT

l         BEFORE / AFTER COMMENT

l         BEFORE / AFTER DDL

l         BEFORE / AFTER GRANT

l         BEFORE / AFTER REVOKE

l         BEFORE / AFTER TRUNCATE

l         AFTER SUSPEND

要创建DDL触发器，需要一定的系统权限，这些权限包括：

l         create trigger

l         create any trigger

l         administer database trigger

l         alter any trigger

l         drop any trigger

DDL触发器可以用于细化的DDL操作审计。一般的做法是设计一张日志表，当DDL触发器激活后，通过系统相关的系统事件函数获取锁需要的信息，然后把数据插入日志表中。这样，就可以记录系统中需要审计的DDL操作。DDL触发器可以针对Database级或者SCHEMA级

要注意的是，DDL触发器仅对以下的对象有效：cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, user。

oracle的trigger：在这里我是以例子的形式演示。

创建两张表：

client：

create table client(
cid number,
name varchar2(35),
address varchar2(80),
taxes varchar2(40)
)

client_log:

create table client_log(
who varchar2(50),
action varchar2(12),
time date
)

 使用触发器谓词
    ORACLE 提供三个参数INSERTING, UPDATING,DELETING 用于判断触发了哪些操作。
谓词
行为
INSERTING
如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
UPDATING
如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
DELETING
如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

创建一个trigger：

create or replace trigger client_trigger

--before触发器
       Before insert or update or delete On client
       declare
       l_action varchar2(30):='---';
       BEGIN
       if inserting then
          l_action:='insert';
       elsif updating then
          l_action:='update';
       elsif deleting  then
           l_action:='delete';
       end if;
       Insert into client_log  Values(user,l_action,sysdate);(监控client，对于client的操作将会在client_log中有所记录。)
       END;

这种监控比较的粗糙，保存了操作的用户，和进行了insert、update、delete哪一种。

更新表中的字段的值操作：

update client set address='商丘' where name='张三'

修改触发器：可以用create or replace trigger client_trigger

删除触发器：DROP TRIGGER trigger_name;

查看触发器：SELECT * FROM USER_SOURCE  WHERE  TYPE='TRIGGER';（直接查到trigger的源代码）

例2：创建登录、退出触发器。

 

CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date timestamp,
 logoff_date timestamp); 

--创建登录触发器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;
--创建退出触发器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;

清除表中的记录。

SQL> truncate
  2  table log_event;
Table truncated

当触发事件发生的时候，可以启动触发器。在触发器中，可以通过DML操作将审计操作记录在日志表中，或者通过抛出一个EXCEPTION来制止某种操作。在触发器中，可以通过使用系统事件函数（Event Attribute Functions）来获取一些信息。以下是系统事件函数的详细情况：

系统事件函数

函数名称

类型

描述

备注

ora_client_ip_address

VARCHAR2

客户端的IP地址

 

ora_database_name

VARCHAR2(50)

数据库名称

 

ora_dict_obj_name

VARCHAR2(30)

DDL发生的对象名称

 

ora_dict_obj_owner

VARCHAR2(30)

DDL发生对象的宿主

 

ora_dict_obj_type

VARCHAR2(20)

对象类别

 

ora_is_alter_column
(column_name IN
VARCHAR2)

BOOLEAN

当某列被修改的时候返回真，否则返回假

 

ora_is_drop_column
(column_name IN
VARCHAR2)

BOOLEAN

当某列被删除的时候返回真，否则返回假

 

ora_login_user

VARCHAR2(30)

登录的用户名

 

ora_sysevent

VARCHAR2(20)

系统事件的名称

 

is_servererror(error_num in integer)

BOLEAN

返回系统是否产生某个错误

1.5.2 DDL触发器概述

通过DDL触发器可以记录特定的DDL操作。DDL触发器是从ORACLE 8i开始具有的触发器类型。在ORACLE 9i中，DDL触发器得到了加强。DDL触发器的触发事件���括：

l         BEFORE / AFTER ALTER

l         BEFORE / AFTER CREATE

l         BEFORE / AFTER DROP

l         BEFORE / AFTER RENAME

l         BEFORE / AFTER ANALYZE

l         BEFORE / AFTER ASSOCIATE STATISTICS

l         BEFORE / AFTER DISASSOCIATE STATISTICS

l         BEFORE / AFTER AUDIT

l         BEFORE / AFTER NOAUDIT

l         BEFORE / AFTER COMMENT

l         BEFORE / AFTER DDL

l         BEFORE / AFTER GRANT

l         BEFORE / AFTER REVOKE

l         BEFORE / AFTER TRUNCATE

l         AFTER SUSPEND

要创建DDL触发器，需要一定的系统权限，这些权限包括：

l         create trigger

l         create any trigger

l         administer database trigger

l         alter any trigger

l         drop any trigger

DDL触发器可以用于细化的DDL操作审计。一般的做法是设计一张日志表，当DDL触发器激活后，通过系统相关的系统事件函数获取锁需要的信息，然后把数据插入日志表中。这样，就可以记录系统中需要审计的DDL操作。DDL触发器可以针对Database级或者SCHEMA级���

要注意的是，DDL触发器仅对以下的对象有效：cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, user。