oracle学习笔记_触发器

淩亂°似流年 2021-09-12 04:06 564阅读 0赞

触发器

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

功能：  
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][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表

\[试验\]  
对修改表的时间、人物进行日志记录。

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;

2、 行触发器  
是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：  
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

[donny_hotmail.com_60_10000_.25]: mailto:‘donny@hotmail.com’,60,10000,.25