Yesterday in the system upgrade, some programs generated intermediate table data rectification work is very annoyed. Due to the operation of the field collection equipment and metering equipment and changes are not controllable. Often caused by non-procedural factors (custom nouns, probably meaning is because the field acquisition equipment power outages, metering equipment replacement, the system basic communication information changes, such as a series of reasons that affect the accuracy of data storage), the impact of the late data statistical analysis of the accuracy and effectiveness. By the customer is found to directly to the marketing department to complain that the system is not stable, ask the site to troubleshoot problems. I wipe! The discontent ceased.
Ultimately, the problem is that there is no need for validation when generating intermediate tables. Simply add a response action to the generated intermediate table in an operation. Later when found in the intermediate table there are some impact statistics (such as daily statistics, in the intermediate table is the existence of the information description of the Kua day). In order to perfect this content, we have tried to create a timed job, scan the intermediate table, and automatically process the information that affects the statistical analysis. Problem data will be stored in the data table for some time, always feel that there is a certain hidden trouble. So like the beginning of the title, for the generation of data in the intermediate table validity, normative, at the expense of the update efficiency of the premise of creating a trigger, when the Metering data table update operation, through the trigger automatically to the intermediate table operation.
Create trigger Trigger_name on {table_name | view_name}--Trigger Action object can be data table or view {for after | Instead of}--trigger type [INSERT, Update,delete]--what happens to activate trigger as Sql_statement --Trigger action
The main emphasis here is on the trigger type, which is divided into two types after and instead of. After can only be bound to a data table, the trigger is activated after an INSERT, UPDATE, delete operation occurs. Instead of translation from English is an alternative, which is understood to activate the trigger before inserting, updating, deleting. It can be bound to a data table or view.
One example: This is the after type trigger, which is triggered when the binding data table has an update operation. The trigger results in adding information to the intermediate table. and the data specification validation is loaded.
-----Create the meterreadingdata t_houserealtimed datasheet Update trigger 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--creating the old instrument data Declare@hrd_ Meternum nvarchar, @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 (), 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&nBsp;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, @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
Add in the Code deleted data table and inserted data table, for the system to provide the operation of virtual table, the following simple sister bask in what circumstances to the operation of the tax bureau stored in which data table.
Insert operation inserted data sheet;
Delete operation delete data table;
the update operation Inserted data tables and Delerte data tables.
Here are some details to note:
(1), DELETE Trigger cannot capture TRUNCATE TABLE statement.
(2), the following Transact-SQL statements are not allowed in triggers:
ALTER Database CREATE database DISK INIT
DISK RESIZE DROP database LOAD database
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG
The trigger in the code is being tested, and the test sample 10W expects to reach the desired level. Also hope that the above content can help you.
This article is from "The growth of Luo shan black tea" blog, please be sure to keep this source http://85608547.blog.51cto.com/2093443/1532281