<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
    

    

    

    







    
        
	



主站蜘蛛池模板：
亚洲黄色在线网站|
亚洲精品美女久久久久久久|
亚洲乱码无人区卡1卡2卡3|
九九久久精品国产免费看小说
|
免费精品国偷自产在线在线|
亚洲第一区精品日韩在线播放|
内射干少妇亚洲69XXX|
精品亚洲福利一区二区|
特级无码毛片免费视频尤物|
国产免费69成人精品视频|
激情内射亚洲一区二区三区|
免费看黄网站在线看
|
中文字幕亚洲专区|
99热亚洲色精品国产88|
a级毛片免费全部播放|
国产成人啪精品视频免费网|
精品日韩亚洲AV无码|
国产免费A∨在线播放|
成人黄18免费视频|
亚洲图片一区二区|
一边摸一边爽一边叫床免费视频|
99在线视频免费观看视频|
国产亚洲无线码一区二区
|
久久性生大片免费观看性|
日韩毛片免费在线观看|
亚洲精品456在线播放|
精品人妻系列无码人妻免费视频|
爽爽日本在线视频免费|
亚洲精品不卡视频|
成人性生交大片免费看好|
国产公开免费人成视频|
亚洲中文字幕久在线|
一级毛片成人免费看免费不卡|
亚洲五月午夜免费在线视频|
亚洲乱色熟女一区二区三区蜜臀|
最近免费2019中文字幕大全|
亚洲中文久久精品无码|
特级毛片在线大全免费播放|
国产精品免费看久久久无码|
亚洲日本人成中文字幕|
91视频免费网址|