oracle 行级触发器

Posted on 2008-09-18 12:23 flustar 阅读(749) 评论(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 - 54, comments - 143, trackbacks - 0, articles - 0

Copyright © flustar