oracle触发器

触发器是特定事件出现的时候， 自动执行的代码块。 类似于存储过程， 触发器与存储过 程的区别在于 :存储过程是由用户或应用程序显式调用的

,而触发器是不能被直接调用的。触

发器经常用于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ， USER_TRIGGERS 数据字典中查到。

功能：

1、允许 /对表的修改

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

3、强制数据一致性

4、提供审计和日志记录

5、防止无效的事务处理

6、启用复杂的业务逻辑

触发器触发时间有两种： after 和 before。

1、触发器的语法：

CREATE [OR REPLACE] TIGGER 触发器名触发时间触发事件

ON 表名

[FOR EACH ROW]

BEGIN

pl/sql 语句

END

其中：

触发器名：触发器对象的名称。

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

该值可取：

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

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

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

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

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

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

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

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

一次。

2、举例：

下面的触发器在更新表 auths 之前触发，目的是不允许在周末修改表：

create triggerauth_secure before insert or update or delete // 对整表更新前触发 on auths begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-2060不能在周末修改表 auths'); end if； end

例子:

CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDA TE OF STAFF_NAME ON CRM.T_SUB_USERINFO

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare begi n

if :NEW.STAFF_NAME!=:OLD.STAFF_NAME the n begi n

・

客户投诉

update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME where S ERVE_SEED=:OLD.SEED;

・

客户关怀

update T_CUSTOMER_CARE set EXECUTOR_NAME=:NEW.STAFF_NAME where EXECUTOR_SEED=:OLD.SEED;

・

客户服务

update T_CUSTOMER_SERVICE set EXECUTOR_NAME=:NEW.STAFF_NAME where EXECUTOR_SEED=:OLD.SEED; end; end if;

end T_sub_useri nfo_aur_ name; /

二Oracle触发器详解

开始：

create triggerbiufer_employees_departme nt_id

before in sertorupdateofdepartme nt_idon employees refere ncin goldasold_value n ewas new_value for each row

whe n (n ew_value.departme nt_id<>80 ) beg in

:n ew_value.commissi on_pct :=0; end; /

1、触发器的组成部分：

1、触发器名称

2、触发语句

3、触发器

4、触发操作

1.1、触发器名称

create trigger biufer_employees_department_id 命名习惯： biufer(before insert update for each row)

employees 表名

department_id 列名

1.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 的时候

1.3、触发器

when (new_value.department_id<>80 )

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

是代表更新之后的值。

1.4、触发操作

是触发器的主体

begin

:new_value.commission_pct :=0; end;

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

触发：

insert into employees(employee_id,last_name,first_name,hire_dat e,job_id,email,

department_id,salary,commission_pct ) values( 12345,'Chen','Donny',sysdate, 12, 60,10000,.25);

‘donny@hotmail.com',

select commission_pct from employees where employee_id=12345; 触发器不会通知用户，便改变

了用户的输入值。

2、触发器的类型有

触发器类型：

1、 语句触发器

2、 行触发器

3、 INSTEAD OF 触发

4、 系统条件触发器

5、 用户事件触发器

2.1、语句级触发器.(语句级触发器对每个 DML语句执行一次)

是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与

INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语 句触发

器都只会针对指定语句激活一次。 比如，无论update多少行，也只会调用一次

语句触发器。

实例：

create or replace trigger tri_test

after in sert or update or delete on test beg in if updating then

dbms_output.put_li ne(' 修改'); elsif deleti ng the n

dbms_output.put_li ne(' 删除'); elsif in sert ing the n

dbms_output.put_li ne(' 插入'); end if; end;

2.2、行级触发器.(行级触发器对 DML语句影响的每个行执行一次

)

实例

触发器

update

行级触发器

create table test(sid nu mber,s name varchar2(20));-- 仓 U建一个

表

create seque nee seq_test;-- 创建序歹 U create or replace trigger tri_test-- before in sert or update of sid on test for each row-- 触发每一行 beg in

if in sert ing the n

select seq_test .n extval in to: new.sid from dual; else

raise_application_error(-20020,' end if; end;

• 测试,插入几条记录

仓 U建触发器

不允许更新 ID 值!');-- 中断程序

in sert into test values(0,'ff); in sert into test values(0,'ff); in sert into test values(0,'tt');

实例

创建一个触发器，无论用户插入新记录，还是修改emp表的job列,都将用户指定 的

job列的值转换成大写.

create or replace trigger trig_job before in sert or update of job on emp for each row beg in

if in sert ing the n

:n ew.job:=upper(: new.job); else :n ew.job:=upper(: new.job); end if; end;

2.3、instead of 触发器.

发器 .)

语法如下 :

（此触发器是在视图上而不是在表上定义的触发器 ,它是用来替换所使用实际语句的触

create or replace

triggertrig_test instead ofinsert or update on 表名

referencing new as n for each row declare begin end;

2.4、模式触发器 .

可以在模式级的操作上建立触发器 .

实例如下 :

create or replace trigger log_drop_obj after drop on schema begin insert into ....... end;

2.5、数据库级触发器 .

可以创建在数据库事件上的触发器 ,包括关闭 ,启动,服务器错误 ,登录等 .这些事件都是实 例范围的 ,不与特定的表或视图关联 .

实例:

create or replace trigger trig_name after startup on database begin end;

2.6、例子：

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

Create table foo(a number); Create trigger biud_foo

Before insert or update or delete On foo Begin

If user not in ( ‘DONN'Y)then Raise_application_error(-20001, is table. ' );

End if; End; /

‘You don'thave access to modify th

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

2.7、［试验］

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

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;

2.8、［试验］

1、修改日志表

alter table employees_log add (action varchar2(20));

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

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;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 /

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