Oracle-trigger

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,‘donny@hotmail.com',60,10000,.25); 
select commission_pct from employees whereemployee_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'thave access to modify this table.'); 
End if; 
End; 
/ 
即使SYS，SYSTEM用户也不能修改foo表 
[试验] 
对修改表的时间、人物进行日志记录。 
1、 建立试验表 
create table employees_copy as select *fromhr.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 shouldnever 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 whereemployee_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, datavarchar2(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 triggerupdate_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 *fromdrop_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

------------------测试-----------------------------

 

create table tbl_trigger_test_a

(

temp_seq number,

temp_a number,

temp_b number,

temp_c number,

temp_d number,

temp_e number

);

create table tbl_trigger_test_b

(

temp_a number,

temp_b number,

temp_c number,

temp_f number,

temp_g number

);

create sequence seq_temp_wjn_0516

minvalue 1

maxvalue 9999999

start with 1

increment by 1

nocache;

create or replace trigger trg_temp_wjn_0516 before

insert or update on tbl_trigger_test_a for each row

begin

  if inserting

    then

    select seq_temp_wjn_0516.nextval into :new.temp_seq from dual;

    end if;

    if updating

      then

            update tbl_trigger_test_b

       set temp_a = :new.temp_a, temp_b = :new.temp_b, temp_c = :new.temp_c

       where temp_a=:old.temp_a;

       end if;

end;

truncate table tbl_trigger_test_a;

truncate table tbl_trigger_test_b;

declare

  n number := 1;

begin

  loop

    insert into tbl_trigger_test_a  (temp_a, temp_b, temp_c, temp_d, temp_e) values (n,n+1,n+2,n+3,n+4);

    n := n + 1;

    if n = 101 then exit;

    end if;

  end loop;

  commit;

end;

declare

  n number := 1;

begin

  loop

    insert into tbl_trigger_test_b  (temp_a, temp_b, temp_c, temp_f, temp_g) values (n,n+1,n+2,n+3,n+4);

    n := n + 1;

    if n = 101 then exit;

    end if;

  end loop;

  commit;

end;

select * from tbl_trigger_test_a;

select * from tbl_trigger_test_b;

declare

  cursor test_a is

    select * from tbl_trigger_test_a;

  n test_a%rowtype;

begin

  for n in test_a

  loop

    update tbl_trigger_test_a

       set temp_b = n.temp_b + 1,

           temp_c = n.temp_c + 1,

           temp_d = n.temp_d + 1,

           temp_e = n.temp_e + 1

     where temp_a = n.temp_a;

  end loop;

  commit;

end;

update tbl_trigger_test_a

   set temp_b = 3,

       temp_c = 4,

       temp_d = 5,

       temp_e = 6

 where temp_a=1;

 commit;

select * from tbl_trigger_test_a;

select * from tbl_trigger_test_b;

drop table tbl_trigger_test_a;

drop table tbl_trigger_test_b;

drop sequence seq_temp_wjn_0516;

--drop trigger trg_temp_wjn_0516;trigger基于的表drop掉,trigger自动drop