十、PL/SQL触发器

1、  概述：

触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块

2、触发器类型

触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行；

而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等

1）替代触发器

由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

2）系统触发器

它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。

3、  触发器组成:

1）触发事件：即在什么情况下触发TRIGGER; 例如：INSERT, UPDATE, DELETE。

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

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

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

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

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

4、  创建触发器

创建触发器的语法：

CREATE[OR REPLACE] TRIGGER trigger_name

BEFORE| AFTER|instead of tri_event

INSERT| DELETE| UPDATE  [OFcolumn [, column …]]}

ON [schema.] table_name|view_name|user_name|db_name

[FOR EACH ROW] [when tri_condition]

Begin

       Plsql_stantences;

trigger_body;

详解：

1）  BEFORE 和AFTER、instend of

指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

Instend of 表示触发器为替代触发器。替换触发器是定义在视图上面的；

用户触发器：是指与DDL操作或者用户登录、推出数据库等事件相关的触发器，如用户登录到数据库或使用alter 语句修改表结构等。

系统触发器：是指在oracle数据库系统的事件中进行触发的触发器，如oracle实例的启动与关闭

Oracle支持：的触发器共计：行触发器、语句触发器、替换触发器、用户触发器、系统触发器5种

2）  FOR EACH ROW选项

说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操做影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE和AFTER触发器为语句触发器，而INSTEAD OF 触发器则为行触发器。

备注：不使用for each row 表示:无论数据库操作影响多少行，触发器只会执行一次

3）  WHEN 子句说明触发约束条件。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

4）  Tri_condition 为一个逻辑表达时，即触发条件表达式，只有当该表达式的值为true时，遇到触发事件才会自动执行触发器。其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。

当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

5）table_name|view_name|user_name|db_name

分别表示：数据表、视图、用户模式、数据库

1)    Plsql_sentences:pl/sql语句，它是触发器功能实现的主体

|实例1|

创建一个触发器：更新时，每一行更新后都会输出hello world

create or replace trigger update1_student_trigger 

after 

       update on student

for each row

begin 

       dbms_output.put_line('hello world');

end;

如何触发该触发器

update student set classid=1 where  xuehao='1';

输出：hello world

|实例2|

使用：old :new修饰符

创建一个触发器：更新时，每一行更新后都会输出更改之前的sal和更改之后的sal

create or replace trigger update2_student_trigger 

after 

       update on student

for each row

begin 

       dbms_output.put_line('old sal:'||:OLD.SAL||','||'new sal:'||:new.sal);

end;

 

--如何删除触发器：

drop  trigger UPDATE2_STUDENT_TRIGGER;

 

|实例3|

编写一个触发器, 在对 t_emp 记录进行删除的时候, 在 t_emp_bak 表中备份对应的记录

步骤1）

create table t_emp (employee_id,name,sal) as select xuehao,xingming,sal from student;

要是写成employee_id number会报错提示：“此 CERATE TABLE 中没有指定列的数据类型”

create table t_emp_bak (employee_id,name,sal) as select xuehao,xingming,sal from studentwhere 1=2;

步骤2)

create or replace trigger bak_emp_trigger

before

delete on t_emp

       for each row

begin

       insert into t_emp_bak values(:old.employee_id, :old.name, :old.sal);

end; 

 

|实例4|

创建一个触发器tri_t_emp，当对t_emp表进行增、改、删的时候，往t_emp_log表中记录操作的事件和操作的类型

create or replace trigger tri_t_emp

         before insert or update or delete

         on t_emp

  declare

         var_tag varchar2(10);--声明一个变量,存储对t_emp表执行的操作类型

  begin

         if inserting then

            var_tag:='插入';

         elsif updating then

            var_tag:='修改';

         elsif deleting then

            var_tag:='删除';

         end if;

         insert into t_emp_log

         values(var_tag,sysdate);

  end tri_dept;

  /