Sql server 2008 觸發器實現

標籤：系統升級   server   create   觸發器   規範性   

    昨天在進行系統升級中，對於某些程式產生的中間表資料的矯正的工作十分惱火。因現場採集裝置和計量裝置運行情況及變更情況不可控。常常造成因非程式因素（自訂名詞，大概的意思就是因為現場採集裝置斷電、計量裝置更換、系統基礎通訊資訊發生變更等一系列認為原因影響資料存放區的準確性），影響後期的資料統計分析的準確性和有效性。不時被客戶發現直接向市場部投訴說系統不穩定，要求現場排查問題。我擦！牢騷終止。

    終究其問題主要是在產生中間表時，沒有必要的驗證。只是單純的在某項操作中，添加的產生中間表的響應操作。後期當發現中間表中存在某些影響統計的資訊（如按日統計用量，在中間表的卻存在誇天的資訊描述）。為了完善該內容，曾經嘗試建立定時作業，對於中間表進行掃描，將影響統計分析的資訊進行自動處理。問題資料還是會有一段時間儲存在資料表中，總覺得存在一定的隱患。所以就像開篇的標題，對於產生中間表中資料有效性、規範性，在犧牲更新效率的前提下建立觸發器，當計量資料表發生更新操作後，通過觸發器自動向中間表進行操作。

    

create trigger trigger_name           on {table_name | view_name}    --   觸發器操作對象 可以是資料表 或視圖   {for After | Instead of }      --   觸發器類型      [ insert, update,delete ]      --   什麼情況啟用觸發器     as               sql_statement               --   觸發器操作

    這裡重點強調一下觸發器類型，主要分為兩種After和Instead of。after 只能綁定到資料表，是在插入、更新、刪除操作發生後，啟用該觸發器。Instead of 從英文直譯過來是替代，理解上就是在進行插入、更新、刪除發生前，啟用該觸發器。其能綁定到資料表或者視圖。

    來一個執行個體看一下：這裡為after類型的觸發器，當綁定資料表發生更新操作時觸發。觸發結果為向中間表添加資訊。並載入了資料規範驗證。

-----建立MeterReadingData 的T_HouseRealtimeD 資料表 Update 觸發器if (object_id(‘tgr_classes_UpdateHouseRealtimeD‘, ‘TR‘) is not null)    drop trigger tgr_classes_UpdateHouseRealtimeDgocreate triggertgr_classes_UpdateHouseRealtimeDonDb_MeterReadingData.dbo.T_HouseRealtimeDfor updateasbeginset nocount on--建立舊的儀錶資料declare@HRD_MeterNum nvarchar(50),@HDI_StartHeatNum  decimal(18,2),@HDI_EndHeatNum  decimal(18,2),@HDI_RunTimeInterval int,@HDI_RunTimeIntervalStart int,@HDI_RunTimeIntervalEnd int,@HDI_StartTime datetime,@HDI_EndTime datetime,@HDI_AvgEntryTemp decimal(18,2),@HDI_AvgEntryTempStart decimal(18,2),@HDI_AvgEntryTempEnd decimal(18,2),@HDI_AvgExportTemp decimal(18,2),@HDI_AvgExportTempStart decimal(18,2),@HDI_AvgExportTempEnd decimal(18,2),@HDI_FluxUsed decimal(18,2),@HDI_FluxUsedStart decimal(18,2),@HDI_FluxUsedend decimal(18,2)--select --HRD_MeterNum,--hrd_cumHeat,--hrd_runtime,--hrd_collecttime,--hrd_EntryTemp,--hrd_ExportTemp,--hrd_cumFlux--from--deleted--select --hrd_cumHeat,--hrd_runtime,--hrd_EntryTemp,--hrd_ExportTemp,--hrd_cumFlux,--hrd_collecttime--from--insertedinsert into Db_MeterReadingData.dbo.T_MeterUseHeatingDetailInformation(HRD_ID,HRD_MeterNum,HDI_StartHeatNum,HDI_EndHeatNum,HDI_RunTimeInterval,HDI_StartTime,HDI_EndTime,HDI_AvgEntryTemp,HDI_AvgExportTemp,HDI_FluxUsed,HDI_isSettlement)select0,inserted.HRD_MeterNum,deleted.hrd_cumHeat,inserted.hrd_cumHeat,(inserted.hrd_runtime-deleted.hrd_runtime),deleted.hrd_collecttime,inserted.hrd_collecttime,(inserted.hrd_EntryTemp+deleted.hrd_EntryTemp)/case when datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)>0 then datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime) else 1 end,(inserted.hrd_ExportTemp+deleted.hrd_ExportTemp)/case when datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)>0 then datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime) else 1 end,(inserted.hrd_cumFlux-deleted.hrd_cumFlux),0fromdeleted inner join insertedondeleted.HRD_MeterNum=inserted.HRD_MeterNumwheredatediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)<=1if exists(select1fromdeleted inner join insertedondeleted.HRD_MeterNum=inserted.HRD_MeterNumwheredatediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)>1)begindeclare @StartTime datetime,@EndTime datetimedeclare @MeterTableID table(--HDI_ID int,MeterID nvarchar(50),StartHeatNum decimal(18,2),EndHeatNum decimal(18,2),runTime int,avgEntryTemp decimal(18,2),avgExportTemp decimal(18,2), fluxUsed decimal(18,2),startTime datetime,endTime datetime)---init parameter--select @StartTime=‘2014-02-06 19:00:00‘,@EndTime=‘2014-02-08 16:00:00‘insert into @MeterTableIDselectinserted.HRD_MeterNum,deleted.hrd_cumHeat,inserted.hrd_cumHeat,(inserted.hrd_runtime-deleted.hrd_runtime),(inserted.hrd_EntryTemp+deleted.hrd_EntryTemp)/case when datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)>0 then datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime) else 1 end,(inserted.hrd_ExportTemp+deleted.hrd_ExportTemp)/case when datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)>0 then datediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime) else 1 end,(inserted.hrd_cumFlux-deleted.hrd_cumFlux),deleted.hrd_collecttime,inserted.hrd_collecttimefromdeleted inner join insertedondeleted.HRD_MeterNum=inserted.HRD_MeterNumwheredatediff(hour,deleted.hrd_collecttime,inserted.hrd_collecttime)<=1        --select M_ID from Db_MeterReadingSys.dbo.T_MeterInformation---init Enddeclare @Count int,@Count_Temp intdeclare Temp_Main cursor forselect MeterID ,StartHeatNum,EndHeatNum ,runTime,avgEntryTemp ,avgExportTemp ,fluxUsed ,startTime,endTimefrom @MeterTableIDdeclare @MeterID nvarchar(50),@StartHeatNum decimal(18,2),@EndHeatNum decimal(18,2),@RunTime int,@avgEntryTemp decimal(18,2),@avgExportTemp decimal(18,2),@fluxUsed decimal(18,2)open Temp_Mainfetch next from Temp_Main into @MeterID ,@StartHeatNum,@EndHeatNum ,@RunTime,@avgEntryTemp ,@avgExportTemp ,@fluxUsed,@StartTime,@EndTimewhile @@FETCH_STATUS=0beginset @Count_Temp=0set @Count=DATEDIFF(HOUR,@StartTime,@EndTime)while @Count_Temp<@Countbegininsert into db_meterReadingData.dbo.T_MeterUseHeatingDetailInformation(HRD_ID,HRD_MeterNum,HDI_StartHeatNum,HDI_EndHeatNum,HDI_RunTimeInterval,HDI_StartTime,HDI_EndTime,HDI_AvgEntryTemp,HDI_AvgExportTemp,HDI_FluxUsed,HDI_isSettlement)select 0,@MeterID,@StartHeatNum+convert(decimal(18,2),(@Count_Temp*(@[email protected])/@Count)),convert(decimal(18,2),@StartHeatNum+((@Count_Temp+1)*(@[email protected])/@Count)),0,DATEADD(HOUR,@Count_Temp,@StartTime),DATEADD(HOUR,@Count_Temp+1,@StartTime),@avgEntryTemp,@avgExportTemp,convert(decimal(18,2),@fluxUsed/@Count),1set @Count_Temp=@Count_Temp+1endfetch next from Temp_Main into @MeterID ,@StartHeatNum,@EndHeatNum ,@RunTime,@avgEntryTemp ,@avgExportTemp ,@fluxUsed,@StartTime,@EndTimeendclose Temp_Maindeallocate Temp_Mainendendset nocount offgo

    補充一下在代碼中deleted資料表和inserted 資料表，為系統提供的操作虛擬表，下面簡單姐曬一下什麼情況下待操作稅局儲存在哪張資料表中。

    Insert 操作 inserted 資料表；

    delete 操作 delete   資料表；

    update 操作 Inserted 資料表和 delerte資料表。

    這裡還要注意一些細節：

        (1)、DELETE 觸發器不能捕獲 TRUNCATE TABLE 語句。 
        (2)、觸發器中不允許以下 Transact-SQL 陳述式： 
        ALTER DATABASE CREATE DATABASE DISK INIT 
        DISK RESIZE DROP DATABASE LOAD DATABASE 
        LOAD LOG RECONFIGURE RESTORE  DATABASE 
        RESTORE LOG 

        代碼中的觸發器正在測試中，測試樣本10W希望等達到預期。也希望以上內容能夠協助到你。

本文出自 “洛山紅茶的成長” 部落格，請務必保留此出處http://85608547.blog.51cto.com/2093443/1532281