Oracle触发器

Oracle触发器

 

 

计数器利用触发器。

一、创建一个表

 createtable STAT_STUDENT

(

  id  NUMBER,

  nm   VARCHAR2(50),

  val  NUMBER,

  typeVARCHAR2(50)

)

 

createorreplacetrigger tr_aiud_stu

  afterinsertorupdateordeleteon student 

  foreachrow

declare

 

begin

    --当是新增时

  if insertingthen

    --更新所有

    UPDATE stat_studentSET val = val +1WHERE id =3;

    --是男

    if :new.sex =1then

      UPDATE stat_studentSET val = val +1WHERE id =1;

      --是女

    elsif :new.sex =0then

      UPDATE stat_studentSET val = val +1WHERE id =2;

    endif;

  endif;

  --当是修改时

  if updatingthen

    --是男

    if :old.sex =1and :new.sex=0then

      UPDATE stat_studentSET val = val -1WHERE id =1;

      UPDATE stat_studentSET val = val +1WHERE id =2;

    endif;

   

    --是女

    if :old.sex =0and :new.sex=1then

      UPDATE stat_studentSET val = val -1WHERE id =2;

      UPDATE stat_studentSET val = val +1WHERE id =1;

    endif;

   

    if :old.sexisnulland :new.sex=1then

     UPDATE stat_studentSET val = val +1WHERE id =1;

    endif;

   

    if :old.sexisnulland :new.sex=0then

     UPDATE stat_studentSET val = val +1WHERE id =2;

    endif;

 

  endif;

 

  --删除时

  if deletingthen

    UPDATE stat_studentSET val = val -1WHERE id =3;

    --是男

    if :old.sex =1then

      UPDATE stat_studentSET val = val -1WHERE id =1;

      --是女

    elsif :old.sex =0then

      UPDATE stat_studentSET val = val -1WHERE id =2;

    endif;

  endif;

end tr_aiud_stu;