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

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

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

    
    

    




    







												


                                            
                                        
                          
     
                                

    
	
  

	
 


	
	
	
	
    
					
	
    
		
    
			
    
				oracle 行級觸發器
			
    
 			Posted on 2008-09-18 12:23 flustar 閱讀(965) 評論(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
    

    

    

    







    
        
	



主站蜘蛛池模板：
国产精品成人无码免费|
一二三四视频在线观看中文版免费
|
亚洲电影免费观看|
亚洲国产成人久久精品影视|
午夜精品一区二区三区免费视频|
亚洲AV永久无码精品一百度影院|
国产成人免费视频|
亚洲精品自拍视频|
69成人免费视频无码专区|
亚洲中文字幕乱码熟女在线|
国外成人免费高清激情视频|
亚洲av无码成人影院一区|
亚洲va中文字幕无码|
中文字幕在线视频免费观看|
久久久久亚洲Av片无码v
|
成人免费无遮挡无码黄漫视频|
亚洲中文字幕无码av|
亚洲第一区精品日韩在线播放|
本免费AV无码专区一区|
久久综合亚洲色一区二区三区|
性短视频在线观看免费不卡流畅|
亚洲精品无码国产片|
久久久久无码专区亚洲av|
可以免费观看的国产视频|
亚洲av永久无码精品三区在线4|
97无码免费人妻超级碰碰碰碰|
青青草国产免费国产是公开|
亚洲va久久久噜噜噜久久狠狠|
四虎在线成人免费网站|
亚洲成在人线在线播放无码
|
免费AA片少妇人AA片直播|
亚洲av无一区二区三区|
亚洲人成图片小说网站|
91网站免费观看|
一级一级毛片免费播放|
亚洲美女免费视频|
四虎永久免费地址在线网站|
男人都懂www深夜免费网站|
亚洲资源最新版在线观看|
老司机亚洲精品影视www|
7723日本高清完整版免费|