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

          

          

    







          
        
	




主站蜘蛛池模板：
灌阳县|
金湖县|
聂荣县|
磐安县|
永年县|
阜康市|
太仆寺旗|
斗六市|
开江县|
巍山|
镇江市|
嘉义市|
南安市|
凭祥市|
湟中县|
万荣县|
镇雄县|
平陆县|
贵溪市|
勃利县|
儋州市|
蕲春县|
永川市|
茂名市|
阳朔县|
黔江区|
荣成市|
乳山市|
白山市|
凌源市|
英吉沙县|
和林格尔县|
聊城市|
大洼县|
曲水县|
五原县|
成都市|
仲巴县|
崇明县|
平舆县|
常熟市|