oracle 行级触发器

Posted on 2008-09-18 12:23 flustar 阅读(959) 评论(0)  编辑  收藏 所属分类: Oracle
    由于项目中业务比较复杂，在代码中实现不太容易，于是就写了一个触发器来完成，第一次写触发器，对oracle的pl/sql  语法感觉不是太习惯，特在此记录一下，以便以后再写的时候，有个参考。
create or replace trigger audit_sync_trigger after

  update or delete on  tbl_video_programme
    
  REFERENCING OLD AS old NEW AS new
    
for each row

declare
    is_audit_new number;/*新的审核值*/
    is_audit_old number;
    category_id_new number;/*新的分类值*/
    category_id_old number;
    keyword_id_new number;/*新的关键字值*/
    keyword_id_old number;
    is_deleted_new number;/*新的是否删除，假删除的值*/
    is_deleted_old number;
begin
    dbms_output.put_line('test!!!!');
    
/*当执行插入操作时*/
    if inserting then
    
        keyword_id_new:=:new.keyword_id;
        
        category_id_new:=:new.category_id;
        
        if keyword_id_new >0 then
        
            update  tbl_keyword k set k.NOT_AUDIT_NUM=k.NOT_AUDIT_NUM+1 where k.KEYWORD_ID=keyword_id_new;
        
        end if;
        
        if category_id_new>0 then
        
            update  tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
            
           -- INSERT INTO TEST VALUES('一条记录被插入了!');
            
        end if;
        
     end if;
     /*当执行更新操作时，主要就是审核和假删除*/
   if updating then
     
        is_audit_new:=:new.is_audit;
        
        is_audit_old:=:old.is_audit;
        
        category_id_new:=:new.category_id;
        
        category_id_old:=:old.category_id;
        
        keyword_id_new:=:new.keyword_id;
        
        keyword_id_old:=:old.keyword_id;
        
        is_deleted_new:=:new.is_deleted;
        
        is_deleted_old:=:old.is_deleted;
       
        /*如果这个节目已经匹配上关键字*/
            if category_id_old >0 then
                 /*如果一个节目被假删除*/
                 
                if is_deleted_new>is_deleted_old then
            
                    if is_audit_old =1 then
                
                        update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                         INSERT INTO TEST VALUES(is_deleted_new);
                         INSERT INTO TEST VALUES(is_deleted_old);
                 
                        if keyword_id_old>0 then
                 
                            update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                 
                        end if;
                    
                    else
                        update tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                    
                        if keyword_id_old>0 then
                    
                            update tbl_keyword k set k.NOT_AUDIT_NUM=k.NOT_AUDIT_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                        
                        end if;
                    
                    end if;
                    
                 end if;
                

            end if;
            
            /*审核一个节目，原来已经匹配上关键字,有分类*/
            if category_id_old>0 then
               -- INSERT INTO TEST VALUES('该关键字原来有分类！');
            /*如果该节目以前未审核*/
                if is_audit_new>is_audit_old then
                   -- INSERT INTO TEST VALUES('审核一个节目由未审核到已审核');
                    /*如果修改了分类*/
                    if category_id_new!=category_id_old then
                         INSERT INTO TEST VALUES('修改了分类！');
                        /*原来分类数-1*/
                         update tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                         /*新的分类数+1*/
                         update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
                         
                          /*如果同时修改了关键字*/
                            if keyword_id_old!=keyword_id_new then
                                  /*原来关键字数-1*/
                                  if keyword_id_old>0 then
                            
                                     update tbl_keyword k set k.NOT_AUDIT_NUM=k.NOT_AUDIT_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                                
                                  end if;
                                  /*新修改关键字数+1*/
                                  if keyword_id_new>0 then
                                
                                      update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_new;
                            
                                  end if;
                         
                            end if;
                            
                    else/*未审核分类不变*/
                            
                         update tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                         
                         update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                         /*如果只修改了关键字*/
                            if keyword_id_old!=keyword_id_new then
                                  /*原来关键字数-1*/
                                  if keyword_id_old>0 then
                            
                                     update tbl_keyword k set k.NOT_AUDIT_NUM=k.NOT_AUDIT_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                                
                                  end if;
                                  /*新修改关键字数+1*/
                                  if keyword_id_new>0 then
                                
                                      update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_new;
                            
                                  end if;
                         
                            else/*如果关键字没被修改*/
                                update tbl_keyword k set k.NOT_AUDIT_NUM=k.NOT_AUDIT_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                                
                                update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_old;
                            
                            end if;
                            
                     end if;
                           
                    
                else /*如果该节目以前审核了*/
                    
                     /*如果修改了分类*/
                    if category_id_new!=category_id_old then
                        /*原来分类数-1*/
                         update tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                         /*新的分类数+1*/
                         update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
                         
                            /*如果也修改了关键字*/
                            if keyword_id_old!=keyword_id_new then
                                 /*原来关键字数-1*/
                                   if keyword_id_old>0 then
                            
                                         update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                                
                                   end if;
                                    /*新修改关键字数+1*/
                                   if keyword_id_new>0 then
                                
                                         update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_new;
                            
                                   end if;
                         
                            end if;
                    else
                         /*如果只修改了关键字*/
                        if keyword_id_old!=keyword_id_new then
                             /*原来关键字数-1*/
                               if keyword_id_old>0 then
                            
                                     update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                                
                               end if;
                                /*新修改关键字数+1*/
                               if keyword_id_new>0 then
                                
                                     update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_new;
                            
                               end if;
                         
                        end if;
                        
                    end if;
                     
                end if;      
                
                
     else/*审核一个节目，该节目没有分类,该节目肯定是未审*/
            
                update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
                
                if keyword_id_new>0 then /*如果审核的时候指定了关键字*/
                
                     update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM+1 where k.KEYWORD_ID=keyword_id_new;
                
                end if;
     end if;       
            
      --INSERT INTO TEST VALUES('一条记录被更新了!');
      dbms_output.put_line('一条记录被更新了!');
     end if;
     
     /*当执行真删除操作时*/
     
    if deleting then
     
        is_audit_old:=:old.is_audit;
        
        category_id_old:=:old.category_id;
        
         keyword_id_old:=:old.keyword_id;
        /*如果删除的节目是已审核的*/
        if is_audit_old=1 then
            
            if category_id_old>0 then
        
                update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old; 
            
            end if;
            
            if keyword_id_old>0 then/*如果该节目匹配上了关键字*/
            
                update tbl_keyword k set k.AUDIT_FINISH_NUM=k.AUDIT_FINISH_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                
            end if;
            
        else
            if category_id_old>0 then
            
                update tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
                
            end if;
            
            if keyword_id_old>0 then/*如果该节目匹配上了关键字*/
            
                update tbl_keyword k set k.NOT_AUDIT_NUM= k.NOT_AUDIT_NUM-1 where k.KEYWORD_ID=keyword_id_old;
                
            end if;
        
        end if;
        
        dbms_output.put_line('一条记录被删除了!');
        --INSERT INTO TEST VALUES('一条记录被删除了!');
     end if;
     
    
end;

只有注册用户登录后才能发表评论。
网站导航:
 

posts - 146, comments - 143, trackbacks - 0, articles - 0

Copyright © flustar