Oracle的触发器

一、触发器简介：

   触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

 

二、触发器的功能：

1、 允许/限制对表的修改

2、 自动生成派生列，比如自增字段

3、 强制数据一致性

4、 提供审计和日志记录

5、 防止无效的事务处理

6、 启用复杂的业务逻辑

 

三、触发器的类型：

      1、DML触发器：  ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

      2、替代触发器：由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE 8专门为进行视图操作的一种处理方法。

     3、系统触发器：ORACLE 8i 提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

 

四、触发器组成：

  触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

  触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

  触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

  触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。  

  触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

  触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

  语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；

  行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

 

五、编写触发器时，需要注意的问题：

1、触发器不接受参数。

2、 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

3、在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

4、触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

5、在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、 DROP）。

6、触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

7、在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

8、在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

9、不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

 

六、定义触发器

create [or replace] tigger 触发器名 触发时间 触发事件

on 表名

  [for each row]

begin

  pl/sql语句

end

说明：

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：

   before：表示在数据库动作之前触发器执行;

   after：表示在数据库动作之后触发器执行。

触发事件：指明哪些数据库动作会触发此触发器：

   insert：数据库插入会触发此触发器;

   update：数据库修改会触发此触发器;

   delete：数据库删除会触发此触发器。

表 名：数据库触发器所在的表。

   for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

 

【例1】 下面的触发器在更新表emp之前触发，目的是不允许在周四修改表：

    SQL> create or replace trigger auth_secure before insert or update or delete

      2  on emp

      3  begin

      4   if(to_char(sysdate,'DY')='星期四') then

      5     raise_application_error(-20600,'不能在周四修改表emp');

      6   end if;

      7  end;

      8  /

     触发器已创建

SQL> insert into emp values(9,'赵云',10000,800);

insert into emp values(9,'赵云',10000,800)

            *

第 1 行出现错误:

ORA-20600: 不能在周四修改表emp

 

【例2】使用触发器实现序号自增

     

     (1) 创建一个测试表：

         create table tab_user(

       id number(11) primary key,

       username varchar(10),

       password varchar(10)

      );

  (2) 创建一个序列：

      create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;

 

   (3) 创建一个触发器：

               create or replace trigger my_tgr before insert on tab_user

       for each row   --对表的每一行触发器执行一次

       declare

          next_id number;

       begin

          select my_seq.nextval into next_id from dual;

          :new.id:=next_id;

       end;

       /

    (4) 向表插入数据:

     insert into tab_user(username,password) values('admin','admin');

     insert into tab_user(username,password) values('fgz','fgz');

     insert into tab_user(username,password) values('test','test');

     COMMIT;

   

   (5)查看结果：

     select * from tab_user;