Oracle系列之七----Trigger
Oracle系列之一----Datatype And Subprogram：http://overshit.iteye.com/admin/blogs/932585；
Oracle系列之二----Exception And Expression：http://overshit.iteye.com/admin/blogs/932605；
Oracle系列之三----Implicit Cursor：http://overshit.iteye.com/admin/blogs/932609；
Oracle系列之四----Dynamic Cursor：http://overshit.iteye.com/admin/blogs/932610；
Oracle系列之五----Cursor And Variable：http://overshit.iteye.com/admin/blogs/932612；
Oracle系列之六----Procedure--Package--Purity：http://overshit.iteye.com/admin/blogs/932615；
Oracle系列之七----Trigger：http://overshit.iteye.com/admin/blogs/932616；
?
----DML Triggerdrop table temp;create table temp( tid number(10), tname varchar2(20))select * from temp;drop sequence seq_temp;create sequence seq_temp;select seq_temp.nextval from dual;----understand trigger:use new keyword as ':new'create or replace trigger tri_temp_insert before insert on temp for each rowdeclarebegin select seq_temp.nextval into :new.tid from dual;end;delete from temp;insert into temp (tname) values ('meilin');insert into temp (tname) values ('bing');insert into temp (tname) values ('ling');----understand trigger:use keyword as ':new' and ':old'create or replace trigger tri_cust_update before update or delete on customer for each rowdeclarebegin if(:old.custage > :new.custage) then raise_application_error('-20001','age can not reduce,add it!'); end if;end;----test expressionupdate customer set custage = '80' where custid = 96;select * from customer where custid = 96;----monitor and decide more than one event in trigger----using predicate:create or replace trigger tri_cust_update_predicate before update or delete on customer for each rowdeclarebegin if updating then if(:old.custage > :new.custage) then raise_application_error('-20001','age can not reduce,add it!'); end if; end if; if deleting then if(:old.custname = '彭海燕') then raise_application_error('-20001','could not delete 彭海燕!'); end if; end if;end;----test expressionupdate customer set custage = '80' where custid = 96;delete from customer where custname = '彭海燕' and custid = 57;drop table temp;create table temp( tid varchar2(10), tname varchar2(20))insert into temp values ('1','meilin');insert into temp values ('2','bing');insert into temp values ('3','qing');select * from temp;select count(*) from temp;create or replace trigger tri_insert_temp before insert on temp for each rowdeclare v_temp_count number;begin select count(*) into v_temp_count from temp; if(v_temp_count >= 3) then raise_application_error(-20001,'the same person more than ' || v_temp_count || ',can not insert!'); end if;end;----test expressioninsert into temp values ('4','rc');----when data change in tbone,then we can operator anther table,in order to achieve referential interity constraintsdrop table tempOne;drop table tempTwo;create table tempOne( tid varchar2(10), toname varchar2(20));create table tempTwo( tid varchar2(10), twname varchar2(20));delete from tempOne;delete from tempTwo;insert into tempOne values ('1','meilinOne');insert into tempOne values ('2','bingOne');insert into tempOne values ('3','qingOne');insert into tempTwo values ('1','meilinTwo');insert into tempTwo values ('2','bingTwo');insert into tempTwo values ('3','qingTwo');select * from tempOne;select * from tempTwo;create or replace trigger tri_update_cascade after update of tid on tempOne for each rowbegin update tempTwo set tempTwo.tid = :new.tid where tempTwo.tid = :old.tid;end;update tempOne t set t.tid = '4' where t.tid = '1';----different of before and after:----you can only use before,because of updating ':new' variabledrop table temp;create table temp( tid number(3), tname varchar2(20))insert into temp values (1,'meilin');insert into temp values (2,'bing');insert into temp values (3,'qing');select * from temp;create or replace trigger tri_only_before before insert on temp for each rowdeclare v_max_tid number(3);begin select nvl(max(tid),0) + 1 into v_max_tid from temp; ----v_max_id := nvl(max(tid),0) + 1; :new.tid := v_max_tid; :new.tname := upper(:new.tname);end;----test expressionselect * from temp;insert into temp (tname) values('tri_before');delete from temp where tid > 3;----insert data into tbtwo when add data in tbone,we can use before or after,because of there is no revises and only use ':new' variable:drop table tbone;drop table tbtwo;create table tbone( tid number(3), tname varchar2(20));create table tbtwo( tid number(3), tname varchar2(20));create or replace trigger tri_before_after after insert on tbone for each rowdeclarebegin insert into tbtwo(tid,tname)values(:new.tid,:new.tname);end;----test expressionselect * from tbone;select * from tbtwo;insert into tbone values(1,'one'); ----different between line level trigger and expression level trigger:drop table tbone;create table tbone( tid number(3), tname varchar2(20));create or replace trigger tri_before_after after delete on tbone ----for each rowdeclarebegin dbms_output.put_line('***');end;----test expressioninsert into tbone values(1,'one');select * from tbone;?
Oracle系列SQL及数据库下载：http://download.csdn.net/source/3046868
?
?
?
?
?
?
?
?
?
?
?
?