SQL Server 2008 Trigger implementation

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 &nbsp, @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