Oracle之触发器

触发器介绍：

    数据库触发器（DataBase Trigger）是存储在数据库中的过程，当表被修改时它隐式的被激发执行。在Oracle中允许在对表进行insert、update和delete操作时隐式的执行所定义的过程，这些过程称为数据库的触发器。

    触发器一般用于：

      1、自动生成导出的列值；

      2、防止无效的事务；

      4、实施更复杂的安全性检查；

      5、在分布式数据库中实施跨越节点的引用完整性；

      6、实施复杂的事务规则；

      7、提供透明事件日志；

      8、提供高级的审计；

      9、维护同步表复制；

      10、收集关于存取表的统计。

触发器的优点：

    1、触发器时自动的：当对表中的数据做了任何修改之后立即被激活（比如手工输入或者应用程序采取的操作）；

    2、触发器可以通过数据库中的相关表进行层叠更改；

    3、触发器可以强制限制，这些限制比用check约束定义的更复杂。

触发器的类型：

    每一触发语句可有三种类型的触发器：

     1、DML触发器：由对表的insert，delete，update激发

     2、INSTEAD  OF触发器：代替直接对视图insert，delete，update操作

     3、系统触发器：a.DDL事件（CREATE，ALTER，DROP语句）需要有ADMISTRATER，DATABASE，TRIGGER特权；

                    b.数据库事件

                        服务器启动，关闭

                        用户登录，注销

                        服务器错误等

创建触发器语法：

   Create [or replace] trigger [模式] 触发器名称

          Before|after   insert|delete(uodate of 列名)

    on 表名

    [for each row]

    [When 条件]

    PL/SQL块      

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

  例如：

   create trigger  T_emp_trigger    

   before insert or update  --定义触发事件

   of ename                 --定义触发字段

   on emp                   --定义需要触发的表

   referencing old as old_value   --定义触发前的数据

               new as new_value   --定义触发后的数据

   for each row                   --触发范围每一行

   when (new_value.ename <>'jack')  --定义触发条件

   begin                            --定义触发内容

       dbms_output.put_line('myRS_emp_trigger is start!');

       :new_value.ename:='Mark';

   end;

参数说明：

  for each row 

    语句级触发器：如果在创建触发器时未使用for each row子句，则该触发器为语句级触发器，该触发器在每个数据修改语句执行后只调用一次，而不管这一操作将影响多少行。

    行级触发器：如果在创建触发器时使用for each row子句，则该触发器为行级触发器，当一个DML操作影响数据库中的多行数据时，对于每一数据行，行级触发器均会被触发一次。

  When条件的出现说明了，在DML操作的时候也许一定会触发触发器，但是触发器    不一定会做实际的工作，比如when后的条件不为真的时候，触发器只是简单的跳过了PL/SQL块；

触发器的原理：

 一、 DML触发器的临时表 :NEW和:OLD

  当用户对数据库做事务处理的操作时，数据系统会产生临时表:NEW和:OLD，这些临时表仅仅存在与当前的DML操作，表的结构与DML操作表的结构一致，存储的值分别是： 

    :NEW ：如果DML是insert或update的操作，则产生:NEW临时表，存储的值是DML操作插入或更改的新值。

    :OLD ：如果DML是delete或update的操作，则产生:OLD临时表，存储的值是DML操作删除或更改前的值。

二、:NEW和:OLD仅用在于行级触发器，不适用与语句级触发器

三、这两个表是动态驻留在内存中，当触发器的工作完成，这两个表也被删除

四、这两个表的主要保存因用户操作而影响到的原始数据值或新数据值

五、这两个表示只读的，用户不能向这两个表写入数据，但是可以引用表中的数据

六、:NEW与:OLD ：必须是针对行级触发器的，也就是说要使用这两个变量的触发器一定有for each row

    这两个变量是系统自动提供的数组变量，:new用来记录新插入的值，:old用来记录被删除的值；

    使用insert的时候只有:new 里面有值；

    使用delete的时候只有:old 里面有值；

    使用update的时候:new和:old 里面都有值；

Instead of 触发器：

  1、当为表或视图定义针对某一操作（insert、delete、update）的instead of类型的触发器且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行而运行的仅是触发器的SQL语句本身。

  2、Instead of触发器主要的优点是使不可修改的视图能够支持修改。其中最典型的是分割视图。为了提高查询的性能，分割视图通常来自多个表的结果集，但是也正是因此而不支持视图的更新。

  3、通过使用逻辑语句以执行批处理的某一部分而放弃执行其余的部分。可以定义触发器在遇到某一错误时，转而执行触发器的另外部分。

  4、instead of insert执行你在触发器里面的代码，替代系统insert操作。

利用instead of触发器，对更新分割视图：

   1、首先建立三个表salemay，salejune，salejuly这三个表分别用来保存五、六、七月的销售量信息：

   create table salemay(|salejune|salejuly)

    (

     sale_id  char(6) nor null,

     sale_name varchar(20),

     sale_qua smallint

    )

  2、建立视图进行联合查询

    create or replace view saleview

      as 

        select * from salemay

        union all

        select * from salejune

        union all

        select * from salejuly; 

  3、在视图上建立instead of insert触发器，当视图上触发insert操作时，通过对Sale_id的分析，判断出插入的记录应该在那个表中进行。其中利用substring（）函数，第一个参数为目标字符串，第二个参数是起始字节，第三个参数是字节的长度。

 Create or replace trigger saleviewtr

   instead of insert

   on saleview 

 Declare

  sale_id varchar(6);

  sale_name varchar2(20);

  sale_qua number(8);

 BEGIN

  sale_id:=:new.sale_id;

  sale_name:=:new.sale_name;

  sale_qua:=:new.sale_qua;

 IF substr(sale_id,1,3)='may' THEN

    INSERT INTO SALEMAY VALUES(sale_id,sale_name,sale_qua);

 END IF;

 IF substr(sale_id,1,3)='jun' THEN

     INSERT INTO salejune  VALUES(sale_id,sale_name,sale_qua);

 END IF;

 IF substr(sale_id,1,3)='jul' THEN

     INSERT INTO salejuly  VALUES(sale_id,sale_name,sale_qua);

 END IF;

END;

在视图上执行插入操作，分别插入以下记录

  insert into saleview values('jul001','www',100)

  go 

  insert into saleview values('jun001','eee',80)

  go

  insert into saleview values('jun001','rrr',50)

  go

然后在相应的表中查看相应的记录。可以看到每个表中均插入了一条记录。

系统时间触发器：

 系统时间触发器是指由数据库系统事件触发的数据库触发器。数据库系统包括以下几种：

  1、数据库的启动（startup）

  2、数据库的关闭（shutdown）

  3、数据库服务器的出错（servererror）

  注意：系统事件触发器不是与特定的表或视图关联。

创建一个系统事件触发器：

 create [or replace] trigger 触发器名称

 {before|after}

 {database_event_list}

 on {database|schema}

 pl/sql语句；

例：创建一个系统事件触发器，记录系统每次的启动时间：

   create table database_log(op_datetimestamp);

   crete or replace trigger 

   database_startup

   after startup

   on database

   begin

      insert into database_log

   values(sysdate);

   end;

用户事件触发器：

 用户事件触发器是指：与数据库定义语句DDL或用户登录/注销等事件相关的触发器，并且可以规定触发时间before和after：

  create,alter,drop,analyze,audit,notaudit,grant,revoke,rename,truncate

只可以指定触发时间before的用户事件：logoff

只可以指定触发时间after的用户事件：logon

 例：创建一个用户事件触发器，记录用户登录系统的用户名与时间

 create table login(who varchar2(20),log

 timestamp);

 create or replace trigger tr_log

 after logon

 on database

 begin

   insert into login  values(user,sysdate);

 end;

ALTER TRIGGER语句：

  alter trigger语句用来重新编译、启用或禁用触发器。如果在触发器内调用了函数或过程，则当这些函数或过程被删除或修改后，触发器的状态将被标识为无效INVALID。

   当触发一个无效的触发器时，Oracle将重新编译触发器代码，如果重新编译时发现错误，这将导致DML语句执行失败。

   在PL/SQL程序中可以调用alter trigger语句，重新编译已经创建的触发器：

 Alter trigger [schema.] trigger_name compile;

 Alter trigger的另外一种用法是禁用和启用触发器；

 Alter trigger [schema.] trigger_name disable|enable;

触发器注意事项：

   1、create trigger语句必须是批处理中的第一个语句；

   2、创建触发器的权限默认分配给表的所有者，且不能将该权限转让给其他用户；

   3、触发器为数据库对象，其名称必须遵循标识符的命名规则；

   4、虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器；

   5、虽然不能再临时表或系统表上创建触发器，但是触发器可以引用临时表；

   6、虽然truncate  table语句类似于没有where子句（用于删除行）的delete语

句，但它并不会引发delete触发器，因为truncate table语句没有记录。

   7、writetext语句不会引发insert或update触发器。

   8、当创建一个触发器时必须指定：a）名称；

                                                         b）在其上定义触发器的表；

                                                         c）触发器将何时激发；

                                                         d） 激活触发器的数据修改语句；