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, ‘[e-mail]’,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表

 

 

[试验]

 

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

 

 

1、 建立试验表

 

create table employees_copy as select *from hr.employees

 

 

2、 建立日志表

 

create table 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 employees_copy

 

       Begin

 

              Insert into employees_log(

 

                     Who,when)

 

              Values( user, sysdate);

 

             

 

       End;

 

       /

 

4、 测试

 

update employees_copy set salary= salary*1.1;

 

 

select *from employess_log;

 

 

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.’);

 

 

              Insert into employees_log(

 

                     Who,action,when)

 

              Values( user, l_action,sysdate);

 

       End;

 

       /

 

 

3、 测试

 

insert into employees_copy( employee_id, last_name, email, hire_date, job_id)

 

       values(12345,’Chen’,’Donny@hotmail’,sysdate,12);

 

 

select *from employees_log