<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


                                            
                                        
                          
     
                                

    
	
  

	
 


	
	
	
	
    
					
	
    
		
    
			
    
				oracle 行級觸發(fā)器
			
    
 			Posted on 2008-09-18 12:23 flustar 閱讀(966) 評論(0)  編輯  收藏  所屬分類: Oracle 
			
			


		
    
		
        由于項目中業(yè)務(wù)比較復(fù)雜，在代碼中實(shí)現(xiàn)不太容易，于是就寫了一個觸發(fā)器來完成，第一次寫觸發(fā)器，對oracle的pl/sql  語法感覺不是太習(xí)慣，特在此記錄一下，以便以后再寫的時候，有個參考。
    

    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;/*新的關(guān)鍵字值*/
    
    keyword_id_old     number;
    
    is_deleted_new     number;/*新的是否刪除，假刪除的值*/
    
    is_deleted_old     number;
    
    begin
    
    dbms_output.put_line(    'test!!!!');
    
    
    
    /*當(dāng)執(zhí)行插入操作時*/
    
        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;
    
         /*當(dāng)執(zhí)行更新操作時，主要就是審核和假刪除*/
    
       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;
    
       
    
            /*如果這個節(jié)目已經(jīng)匹配上關(guān)鍵字*/
    
                if category_id_old >0 then
    
                     /*如果一個節(jié)目被假刪除*/
    
                 
    
                    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;
    
            
    
                /*審核一個節(jié)目，原來已經(jīng)匹配上關(guān)鍵字,有分類*/
    
                if category_id_old>0 then
    
                   -- INSERT INTO TEST VALUES('該關(guān)鍵字原來有分類！');
    
                /*如果該節(jié)目以前未審核*/
    
                    if is_audit_new>is_audit_old then
    
                       -- INSERT INTO TEST VALUES('審核一個節(jié)目由未審核到已審核');
    
                        /*如果修改了分類*/
    
                        if category_id_new!=category_id_old then
    
                             INSERT INTO TEST VALUES('修改了分類！');
    
                            /*原來分類數(shù)-1*/
    
                             update tbl_programme_category pc set pc.NOT_AUDIT_NUM=pc.NOT_AUDIT_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
    
                             /*新的分類數(shù)+1*/
    
                             update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
    
                         
    
                              /*如果同時修改了關(guān)鍵字*/
    
                                if keyword_id_old!=keyword_id_new then
    
                                      /*原來關(guān)鍵字?jǐn)?shù)-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;
    
                                      /*新修改關(guān)鍵字?jǐn)?shù)+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;
    
                             /*如果只修改了關(guān)鍵字*/
    
                                if keyword_id_old!=keyword_id_new then
    
                                      /*原來關(guān)鍵字?jǐn)?shù)-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;
    
                                      /*新修改關(guān)鍵字?jǐn)?shù)+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/*如果關(guān)鍵字沒被修改*/
    
                                    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 /*如果該節(jié)目以前審核了*/
    
                    
    
                         /*如果修改了分類*/
    
                        if category_id_new!=category_id_old then
    
                            /*原來分類數(shù)-1*/
    
                             update tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM-1 where pc.PROGRAMME_CATEGORY_ID=category_id_old;
    
                             /*新的分類數(shù)+1*/
    
                             update  tbl_programme_category pc set pc.TOTAL_NUM=pc.TOTAL_NUM+1 where pc.PROGRAMME_CATEGORY_ID=category_id_new;
    
                         
    
                                /*如果也修改了關(guān)鍵字*/
    
                                if keyword_id_old!=keyword_id_new then
    
                                     /*原來關(guān)鍵字?jǐn)?shù)-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;
    
                                        /*新修改關(guān)鍵字?jǐn)?shù)+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
    
                             /*如果只修改了關(guān)鍵字*/
    
                            if keyword_id_old!=keyword_id_new then
    
                                 /*原來關(guān)鍵字?jǐn)?shù)-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;
    
                                    /*新修改關(guān)鍵字?jǐn)?shù)+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/*審核一個節(jié)目，該節(jié)目沒有分類,該節(jié)目肯定是未審*/
    
            
    
                    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 /*如果審核的時候指定了關(guān)鍵字*/
    
                
    
                         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;
    
     
    
         /*當(dāng)執(zhí)行真刪除操作時*/
    
     
    
        if deleting then
    
     
    
        is_audit_old:    =:old.is_audit;
    
        
    
        category_id_old:    =:old.category_id;
    
        
    
         keyword_id_old:    =:old.keyword_id;
    
            /*如果刪除的節(jié)目是已審核的*/
    
            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/*如果該節(jié)目匹配上了關(guān)鍵字*/
    
            
    
                    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/*如果該節(jié)目匹配上了關(guān)鍵字*/
    
            
    
                    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;
    

    

    
	
    


    
	    
    




    





    


    

	
    
		
    
				
		
	
		
    
		
		
    只有注冊用戶登錄后才能發(fā)表評論。
    
		
    
			
		
    
			
		
    
		
		
    
		


    

		    		
		
    
		
    			
		
		
		
		
    
		
    
		
		
    
		
    
			網(wǎng)站導(dǎo)航:
		
		
    
		
    
			


		
    	
		
    
			 
		
    
		
    
			
		
    
		
    
			
				
		
    		
	
    	

    



    

				
    
	
	
	

    
	
    
posts - 146, comments - 143, trackbacks - 0, articles - 0
    
	
    Copyright © flustar
    

    

    

    







    
        
	



主站蜘蛛池模板：
亚洲av无码成人精品区在线播放|
妻子5免费完整高清电视|
免费人成无码大片在线观看|
亚洲精品GV天堂无码男同|
最近高清国语中文在线观看免费|
亚洲免费人成视频观看|
蜜臀98精品国产免费观看|
亚洲激情校园春色|
大地资源在线观看免费高清|
亚洲综合色丁香婷婷六月图片|
精品久久久久久久免费人妻|
亚洲av色香蕉一区二区三区蜜桃|
又黄又爽的视频免费看|
一级中文字幕乱码免费|
国产亚洲精久久久久久无码77777|
青柠影视在线观看免费|
亚洲狠狠久久综合一区77777|
99热在线观看免费|
亚洲区视频在线观看|
女人被弄到高潮的免费视频
|
精品日韩亚洲AV无码一区二区三区
|
亚洲精品V天堂中文字幕|
免费看一级做a爰片久久|
一级毛片不卡免费看老司机|
中文亚洲AV片不卡在线观看|
麻豆精品不卡国产免费看|
4480yy私人影院亚洲|
成人毛片18岁女人毛片免费看|
免费很黄无遮挡的视频毛片|
亚洲成a人片77777kkkk|
国产免费av片在线看|
深夜福利在线视频免费|
中文字幕亚洲第一在线|
日韩免费a级在线观看|
中国一级全黄的免费观看|
亚洲人成电影在线观看网|
国产免费直播在线观看视频|
99免费精品视频|
亚洲jizzjizz在线播放久|
亚洲成av人片不卡无码久久|
99热在线免费观看|