ORCALE 触发器_初级进阶篇

触发器

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

 

功能：
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

 

update employees_copy set salary=50000 where employee_id = 12345;

 

 

行触发器

是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：

1、 定义语句中包含for each row子句
2、 在before……for each row触发器中，用户可以引用受到影响的行值。

比如：

定义：

create trigger biufer_employees_department_id

 

before insert or update

of department_id

 

on employees_copy

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;

referencing 子句：

执行dml语句之前的值的默认名称是 :old ,之后的值是 :new
insert 操作只有:new
delete 操作只有 :old
update 操作两者都有

referencing子句只是将new 和old重命名为new_value和old_value，目的是避免混淆。比如操作一个名为new的表时。
作用不很大。

 

 

：为主健生成自增序列号

drop table foo;

create table foo(id number, data varchar2(20));

create sequence foo_seq;

 

create or replace trigger bifer_foo_id_pk

before insert on foo

for each row

begin

select foo_seq.nextval into :new.id from dual;

end;

 

insert into foo(data) values(‘donny’);

insert into foo values(5,’chen’);

select * from foo;

 

 

3、 instead of 触发器更新视图

 

create or replace view company_phone_book as

select first_name||’, ’||last_name name, email, phone_number,

employee_id emp_id

from hr.employees;

 

尝试更新email和name

update hr.company_phone_book

set name=’chen1, donny1’

where emp_id=100

 

create or replace trigger update_name_company_phone_book

instead of

update on hr.company_phone_book

begin

update hr.employees

set employee_id=:new.emp_id,

first_name=substr(:new.name, instr(:new.name,’,’)+2),

last_name= substr(:new.name,1,instr(:new.name,’,’)-1),

phone_number=:new.phone_number,

email=:new.email

where employee_id=:old.emp_id;

end;

 

4、 系统事件触发器
系统事件：数据库启动、关闭，服务器错误

 

create trigger ad_startup

after startup

on database

begin

-- do some stuff
end;

5、 用户事件触发器
用户事件：用户登陆、注销，create / alter / drop / analyze / audit / grant / revoke / rename / truncate / logoff

例子：记录删除对象

1. 日志表
create table droped_objects( object_name varchar2(30), object_type varchar2(30), dropped_on date);

 

2．触发器

create or replace trigger log_drop_trigger

before drop on donny.schema

begin

insert into droped_objects values(

ora_dict_obj_name, -- 与触发器相关的函数

ora_dict_obj_type,

sysdate);

end;

3. 测试
create table drop_me(a number);

create view drop_me_view as select *from drop_me;

drop view drop_me_view;

drop table drop_me;

 

select *from droped_objects

禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;

 

事务处理：
在触发器中，不能使用commit / rollback
因为ddl语句具有隐式的commit，所以也不允许使用

 

视图：
dba_triggers

oracle没有sqlserver那种机制，只能对字段进行比较判断是否更新
create or replace trigger tri_t

before update on t for each row

when (new.bb<>old.bb)--比较BB字段是否更新

begin

:new.cc:=1;
end;

 

ORACLE触发器语法
触发器有两种after和before，区别在于每次提交事务触发一次和每一行数据的更新都触发一次，使用时候要注意，以免达不到目的
ORACLE产生数据库触发器的语法为：

create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
pl/sql 语句

 

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

触发时间：指明触发器何时执行，该值可取：

 

 

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

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

 

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

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

 

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

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

 

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

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

 

 

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

create trigger auth_secure

before insert or update or delete //对整表更新前触发

on auths

begin

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

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');--触发器中只能抛异常实现回滚

end if;

end ;