<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
    

    

    

    







    
        
	



主站蜘蛛池模板：
男男gvh肉在线观看免费|
亚洲综合激情五月色一区|
亚洲欧洲国产精品久久|
久久亚洲最大成人网4438|
亚洲国产精品无码观看久久|
欧美亚洲国产SUV|
好湿好大好紧好爽免费视频|
99视频在线看观免费|
四虎国产精品免费久久|
国产免费牲交视频|
亚洲乱码一区二区三区在线观看|
亚洲视频免费播放|
亚洲AV无码片一区二区三区|
中文字幕a∨在线乱码免费看
|
免费毛片在线看不用播放器
|
国产精品免费观看调教网|
青娱乐免费视频在线观看|
四虎AV永久在线精品免费观看|
亚洲综合色视频在线观看|
2022年亚洲午夜一区二区福利|
亚洲人成欧美中文字幕|
中国好声音第二季免费播放|
亚洲三级高清免费|
亚洲国产综合无码一区二区二三区|
亚洲一区二区女搞男|
亚洲国产美女福利直播秀一区二区
|
亚洲性猛交XXXX|
亚洲黄色激情视频|
国产国产人免费人成成免视频|
1000部免费啪啪十八未年禁止观看|
国产美女无遮挡免费网站|
久久亚洲一区二区|
国产精品亚洲专区一区|
男人都懂www深夜免费网站|
国产精品冒白浆免费视频|
亚洲AV无码乱码在线观看裸奔|
精品久久久久久久久亚洲偷窥女厕|
男人j进入女人j内部免费网站|
免费无码不卡视频在线观看|
日本亚洲欧洲免费天堂午夜看片女人员
|
亚洲精品在线不卡|