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

?

?

?

?

?

?

?

?

?

?

?

?