oracle 触发器小记

 

触发器如存储过程，但最大的区别是

他不能有用户进行显示的触发或者由应用程序进行触发。

以oracle为例

==================================================================================

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

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

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

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

 

 

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

--对整表更新前触发

create or replace trigger auth_secure 

before insert or update or delete 

on users

begin

if(to_char(sysdate,'DY'))='星期天' then

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表');

end if;

end ;

/

 

2语句触发器

create table foo (id number(9));

 

create or replace trigger tri_foo

before insert or update or delete 

on foo

begin 

if user not in('lizx') then

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

end if;

end ;

/

--测试

用system用户，对foo表进行插入或更改有提示错误

 

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

建立实验表

create table foo_copy as select * from foo where 1=2;

create table foo_log (who varchar2(30),when date);

commit;

create or replace trigger foo_tri

before insert or update or delete 

on foo_copy

begin 

insert into foo_log values(user,sysdate);

end;

/

 

insert into foo_copy values(1);

 

4。记录动作语句，是insert 还是update还是delete

alter table foo_log add (action varchar2(30));

 

create or replace trigger foo_tri_action

before insert or update or delete 

on foo_copy

Declare 

v_action foo_log.action%type;

begin 

 if inserting then

   v_action := 'Insert';

  elsif updating then

   v_action :='Update';

  elsif deleting then

   v_action := 'Delete';

  else

   raise_application_error(-20001,'You should never ever get this error.');

end if;

  insert into foo_log values(user,sysdate,v_action);

end;

/

 

 

insert into foo_copy values(1);

 

注意:

建立了该触发器后，上面的触发器就不能用了，原因是foo_log的表结构给改了。

oracle中触发器的启用禁用：

表名称：table_name,两个触发器名称 trigger1_table_name,trigger2_table_name

1.以trigger的owner登录sqlplus。

2.禁用表table_name的所有trigger。

sql> alter table table_name disable all triggers;

Table altered.

3.启用表table_name的所有trigger。

sql> alter table table_name enable all triggers;

Table altered.

4.禁用表table_name的trigger1_table_name。

sql>  alter trigger trigger1_table_name disable;

Trigger altered.

5.启用表table_name的trigger1_table_name。

sql>  alter trigger trigger1_table_name enable;

Trigger altered

 

===========================

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

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

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

create sequence foo_seq;

 

create or replace trigger bifer_foo_id_pk

 before insert on foo2

 for each row

begin

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

end;

/

 

insert into foo2(data) values('donny');

insert into foo2 values(5,'Chen'); --5不起作用，新记录id=2

select * from foo2;

 

==============================================

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

 

=============================================

7

系统事件触发器

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

 

create trigger ad_startup

 after startup

  on database

begin

 -- do some stuff

end;

/

 

 

 用户事件触发器

用户事件：用户登陆、注销，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

 

 

 

 

====================================================

事务处理：

在触发器中，不能使用commit / rollback

因为ddl语句具有隐式的commit，所以也不允许使用

 

视图：

dba_triggers