DML觸發(fā)器學(xué)習(xí)
?
??? 來了解一下觸發(fā)器的建立方法，以及各類觸發(fā)器的簡(jiǎn)單介紹。首先看DML觸發(fā)器的語法：
??? CREATE [OR REPLACE] TRIGGER trigger_name
??? {BEFORE | AFTER }
??? {INSERT | DELETE | UPDATE [OF column [, column ...]]}
??? ON {[schema.] table_name | [schema.] view_name}
??? [PRFERENCING {OLD [AS] old | NEW [AS] new | PARENT as parent}]
??? [FOR EACH ROW ]
??? [WHEN condition]
??? Trigger body;
?
??? 說明：
??? 1、BEFORE/AFTER：指觸發(fā)時(shí)間在DML操作之前還是之后(對(duì)表配置約束時(shí)特別有用)
??? 2、DML包括INSERT、DELETE、UPDATE(可以只針對(duì)某幾列)
??? 3、FOR EACH ROW：表明是行級(jí)觸發(fā)器
?
?
DML觸發(fā)器示例
?
??? 1、創(chuàng)建信息表和最終表
?
??? create table t11(a int,b varchar2(100));
??? insert into t11 values(1,'aaa');
??? insert into t11 values(1,'bbb');
??? insert into t11 values(1,'ccc');
??? insert into t11 values(2,'ddd');
??? insert into t11 values(2,'eee');
??? insert into t11 values(3,'fff');
??? insert into t11 values(4,'ggg');
??? insert into t11 values(5,'hhh');
??? commit;
?
??? create table t1_log(
?????? who varchar2(30),
?????? poer_date date
?????? );
?
??? 2、創(chuàng)建觸發(fā)器
?
??? create or replace trigger delete_trigger
????? after delete
????? on t11
????? for each row --行級(jí)觸發(fā)器
??? begin
????? insert into t1_log values(user,sysdate);
??? end;
?
??? 3、測(cè)試
?
??? delete from t11 where a=1;
?
??? SQL> select * from t1_log;
?
??? WHO????? POER_DATE
??? ------- -------------------
??? WXQ????? 2008-11-2 14:04:38
??? WXQ????? 2008-11-2 14:04:38
??? WXQ????? 2008-11-2 14:04:38
?
??? 注：若沒有for each row語句，則只插入一行記錄。
?
?
觸發(fā)器的級(jí)聯(lián)
?
??? create table t1_a(id int);
??? create table t1_b(id int);
??? create table t1_c(id int);
?
??? create trigger tr_a
????? after insert
????? on t1_a
??? begin
????? insert into t1_b values(1);
??? end; --插入t1_a時(shí)，插入t1_b
?
??? create trigger tr_b
????? after insert
????? on t1_b
??? begin
????? insert into t1_c values(1);
??? end; --插入t1_b時(shí)，插入t1_c
?
??? create trigger tr_c
????? after insert
????? on t1_c
??? begin
????? update tr_a set tr_a.id=tr_a.id+10;
??? end; --插入t1_c時(shí)，更新t1_a
?
??? 測(cè)試：
?
??? SQL> insert into t1_a values(5);
??? 1 row inserted
?
??? SQL> select * from t1_a;
?????? ID
??? -----
?????? 15
?
?
多條件觸發(fā)器
?
??? CREATE OR REPLACE TRIGGER
??? BEFORE INSERT OR UPDATE OR DELETE
??? BEGIN
????? IF inserting THEN
????? --insert語句觸發(fā)
????? ELSIF updating THEN
????? --update語句觸發(fā)
????? ELSIF deleting THEN
????? --delete語句觸發(fā)
????? END IF;
??? END;
?
??? 注：若要針對(duì)update某一列，則用 IF UPDATING('xx') THEN...
?
??? 具體舉例：
?
??? create or replace trigger oper_trigger
????? before insert or update or delete
????? on t1
??? declare
????? str_action varchar2(100);
??? begin
????? if inserting then
??????? str_action:='Insert';
????? end if;
????? if updating then
??????? str_action:='Update';
????? end if;
????? if deleting then
??????? str_action:='Delete';
????? end if;
????? insert into t1_log values(str_action,sysdate);
??? end;
?
?
?
行級(jí)觸發(fā)器特性
?
1、簡(jiǎn)單舉例
?
??? create or replace trigger reco_trigger
????? after delete
????? on t11
????? for each row
??? begin
????? insert into t22 values(:old.a,:old.b);
??? end;
?
2、說明：
?
??? * 可以通過:new和:old來獲得操作前后的不同數(shù)據(jù)映像
??? * update可同時(shí)使用:new、:old，insert只能用:new，delete只能用:old
?
3、通過REFERENCING修改映像標(biāo)識(shí)符
?
??? create or replace trigger reco_trigger
????? before update
????? on t11
????? referencing new as my_value --注意格式
????? for each row
????? when (my_value.a > 10) --新值大于10時(shí)才觸發(fā)
??? begin
????? insert into t22 values(:my_value.a,:my_value.b);
??? end;
?
??? 注意：即使重命名了，但是還是要在前面加“:”，但是在referencing和when字句中不用加
?
?
?