Oracle触发器

触发器

是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

功能：

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

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

3、强制数据一致性 

4、提供审计和日志记录  

5、防止无效的事务处理

6、启用复杂的业务逻辑

开始

create trigger biufer_employees_department_id  

       before insert or update

              of department_id

              on employees  

       referencing old as old_value

                      new as new_value

       for each row  

       when (new_value.department_id<>80 )

begin

       :new_value.commission_pct :=0;

end;  

/

触发器的组成部分：

1、触发器名称  

2、触发语句  

3、触发器限制  

4、触发操作

 

1、触发器名称 

create trigger biufer_employees_department_id  

命名习惯：

biufer（before insert update for each row）  

employees 表名

department_id 列名

2、触发语句

比如：  

表或视图上的DML语句  

DDL语句

数据库关闭或启动,startup shutdown 等等

before insert or update

              of department_id

              on employees

       referencing old as old_value

                      new as new_value

       for each row

说明：  

1、无论是否规定了department_id ，对employees表进行insert的时候

2、对employees表的department_id列进行update的时候

3、触发器限制 

when (new_value.department_id<>80 )

限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。  

其中的new_value是代表更新之后的值。

4、触发操作 

是触发器的主体

begin

       :new_value.commission_pct :=0;  

end;

主体很简单，就是将更新后的commission_pct列置为0  

触发：  

insert into employees(employee_id,  

last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )  

values( 12345,’Chen’,’Donny’, sysdate, 12,‘donny@hotmail.com’,60,10000,.25);

select commission_pct from employees where employee_id=12345;

  

触发器不会通知用户，便改变了用户的输入值。

  

触发器类型： 

1、语句触发器  

2、行触发器  

3、INSTEAD OF 触发器  

4、系统条件触发器

5、用户事件触发器

1、语句触发器

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。

例子：  

需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。

Create table foo(a number);

Create trigger biud_foo

       Before insert or update or delete  

              On foo

Begin

       If user not in (‘DONNY’) then

              Raise_application_error(-20001, ‘You don’t have access to modify this table.’);

       End if;

End; 

/

即使SYS，SYSTEM用户也不能修改foo表

[试验]

如下实验环境OS: XP  DB:ORACLE 10G 

对修改表的时间、人物进行日志记录。

   1、建立试验表  

create table scott.employees_copy as select *from scott.emp

2、建立日志表

create table scott.employees_log( who varchar2(30), when date); 

    3、在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。

Create or replace trigger biud_employee_copy 
              Before insert or update or delete 
                     On scott.employees_copy 
       Begin 
              Insert into scott.employees_log( 
                     Who,when)  
              Values( user, sysdate); 
       End;  
       /  

  

4、测试

 

update scott.employees_copy set sal= sal*1.1; 

SQL> select * from scott.employees_log;

WHO                            WHEN
------------------------------ --------------
SCOTT                          23-11月-07

5、确定是哪个语句起作用？  

即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？

可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断：

begin

        if inserting then

               -----  

        elsif updating then

               -----

        elsif deleting then

                ------

        end if;

end;

if updating(‘COL1’) or updating(‘COL2’) then

        ------

end if;

[试验]

1、修改日志表

alter table employees_log

        add (action varchar2(20));

2、修改触发器，以便记录语句类型。

Create or replace trigger biud_employee_copy 
              Before insert or update or delete 
                    On employees_copy 
       Declare 
              L_action employees_log.action%type; 
       Begin 
        if inserting then 
               l_action:='Insert'; 
        elsif updating then 
               l_action:='Update'; 
        elsif deleting then 
               l_action:='Delete'; 
        else 
               raise_application_error(-20001,'You should never ever get this error.');  
        end if;
              Insert into employees_log( 
                     Who,action,when) 
              Values(user, l_action,sysdate); 
       End; 
       / 

3、测试  

SQL> insert into employees_copy(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  2  values(111,'Guan','manager',8080,'18-11月-83',9999,9999,10);

已创建 1 行。

SQL> select *from employees_log;

WHO                            WHEN           ACTION
------------------------------ -------------- --------------------
SCOTT                          23-11月-07     Insert
SCOTT                          23-11月-07