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 Trigger
drop 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 row
declare
begin
  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 row
declare
begin
  if(:old.custage > :new.custage) then
    raise_application_error('-20001','age can not reduce,add it!');
  end if;
end;
----test expression
update 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 row
declare
begin
  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 expression
update 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 row
declare
  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 expression
insert into temp values ('4','rc');

----when data change in tbone,then we can operator anther table,in order to achieve referential interity constraints
drop 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 row
begin
  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' variable
drop table temp;
create table temp(
  tid number(3),
  tname varchar2(20)
)
insert into temp values (1,'meilin');
insert into temp values (2,'bing');
insert int