触发器(十三)Trigger_Insert_VocMin

USE [AirDB_YiZhuang]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_VocMin]    Script Date: 2017/12/26 8:38:28 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author: pyy
-- Create date: 2016-10-04
-- Description: [PS].[T_Bas_GasRealTimeData]表中插入数据时，向[AirDB_YiZhuang].[dbo].[T_Data_VocMin]中插入数据
-- mondify date: 2017-3-17
-- mondify Description: 触发太频繁（每分钟）影响了源数据[PS].[T_Bas_GasRealTimeData]的实时接入改成[dbo].[T_Mid_AWSHour]（每小时）
-- =============================================
create TRIGGER [dbo].[Trigger_Insert_VocMin]--创建的触发器名称
   ON  [dbo].[T_Mid_AWSHour]  --触发器基于的表[PS].[T_Bas_GasRealTimeData]
   AFTER INSERT --触发类型:插入时触发
AS 
BEGIN
declare @t1 datetime
declare @t2 datetime
declare @Mint datetime
--站点1的最新时间
set @t1=(select max(monitortime) from [DB_Air_Product].[PS].[T_Bas_GasRealTimeData] where EntCode= '130300001')
--站点2的最新时间
set @t2=(select max(monitortime) from [DB_Air_Product].[PS].[T_Bas_GasRealTimeData] where EntCode= '130300002')
--最新时间里的最小值
set @Mint=(select case when @t1<@t2 then @t1 else @t2 end)


insert into [AirDB_YiZhuang].[dbo].[T_Data_VocMin](STATIONCODE,MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane)
--top 13
select  case a.STATIONCODE
when '130300002' then '500000001'--编号写错了5000001
  when '130300001' then '500000002'--5000002
  else null end 
  STATIONCODE,MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane  
  from (SELECT 
EntCode STATIONCODE,MonitorTime MONIDATE
 ,ROUND(max(case when  PollutantCode='670' then Strength else null end)*1000,3)  Vinyl_chloride
 ,ROUND(max(case when  PollutantCode='647' then Strength else null end)*1000,3) Butadiene
 ,ROUND(max(case when  PollutantCode='659' then Strength else null end)*1000,3) Ethylene
  ,ROUND(max(case when  PollutantCode='658' then Strength else null end)*1000,3) ethane
 ,ROUND(max(case when  PollutantCode='635' then Strength else null end)*1000,3) Propane
  ,ROUND(max(case when  PollutantCode='636' then Strength else null end)*1000,3) propylene
   ,ROUND(max(case when  PollutantCode='641' then Strength else null end)*1000,3) butylene 
    ,ROUND(max(case when  PollutantCode='640' then Strength else null end)*1000,3)  twoButene
 ,ROUND(max(case when  PollutantCode='643' then Strength else null end)*1000,3) cistwoButene
 ,ROUND(max(case when  PollutantCode='648' then Strength else null end)*1000,3) transtwoPentene
  ,ROUND(max(case when  PollutantCode='650' then Strength else null end)*1000,3) cistwoPentene
 ,ROUND(max(case when  PollutantCode='649' then Strength else null end)*1000,3) onePentene
  ,ROUND(max(case when  PollutantCode='653' then Strength else null end)*1000,3) Isoprene
   ,ROUND(max(case when  PollutantCode='638' then Strength else null end)*1000,3) IsoButene


    ,ROUND(max(case when  PollutantCode='639' then Strength else null end)*1000,3)  nbutane
 ,ROUND(max(case when  PollutantCode='645' then Strength else null end)*1000,3) Isopentane
 ,ROUND(max(case when  PollutantCode='646' then Strength else null end)*1000,3) nPentane
  ,ROUND(max(case when  PollutantCode='651' then Strength else null end)*1000,3) dimethylbutane
 ,ROUND(max(case when  PollutantCode='661' then Strength else null end)*1000,3) symDichloroethane
  ,ROUND(max(case when  PollutantCode='601' then Strength else null end)*1000,3) Methylpentane
   ,ROUND(max(case when  PollutantCode='603' then Strength else null end)*1000,3) nHexane 
    ,ROUND(max(case when  PollutantCode='605' then Strength else null end)*1000,3)  Benzene
 ,ROUND(max(case when  PollutantCode='656' then Strength else null end)*1000,3) cyclohexane
 ,ROUND(max(case when  PollutantCode='610' then Strength else null end)*1000,3) nHeptane
  ,ROUND(max(case when  PollutantCode='616' then Strength else null end)*1000,3) octane
 ,ROUND(max(case when  PollutantCode='621' then Strength else null end)*1000,3) nNonane
  ,ROUND(max(case when  PollutantCode='622' then Strength else null end)*1000,3) Cumene
   ,ROUND(max(case when  PollutantCode='623' then Strength else null end)*1000,3) nPropylbenzene


    ,ROUND(max(case when  PollutantCode='739' then Strength else null end)*1000,3)  trimethylbenzene
 ,ROUND(max(case when  PollutantCode='739' then Strength else null end)*1000,3) Mesitylene
 ,ROUND(max(case when  PollutantCode='668' then Strength else null end)*1000,3) Chlorobenzene
  ,ROUND(max(case when  PollutantCode='688' then Strength else null end)*1000,3) jiandichlorobenzene
 ,ROUND(max(case when  PollutantCode='673' then Strength else null end)*1000,3) duidichlorobenzene
  ,ROUND(max(case when  PollutantCode='674' then Strength else null end)*1000,3) lindichlorobenzene
   ,ROUND(max(case when  PollutantCode='667' then Strength else null end)*1000,3) tetrachloroethylene 
    ,ROUND(max(case when  PollutantCode='665' then Strength else null end)*1000,3)  trichloroethylene
 ,ROUND(max(case when  PollutantCode='644' then Strength else null end)*1000,3) cyclopentane
 ,ROUND(max(case when  PollutantCode='629' then Strength else null end)*1000,3) decane
  ,ROUND(max(case when  PollutantCode='631' then Strength else null end)*1000,3) Diethylbenzene
 ,ROUND(max(case when  PollutantCode='631' then Strength else null end)*1000,3) duiDiethylbenzene
  ,ROUND(max(case when  PollutantCode='607' then Strength else null end)*1000,3) ttdimethylpentane
   ,ROUND(max(case when  PollutantCode='604' then Strength else null end)*1000,3) tfdimethylpentane
   
 ,ROUND(max(case when  PollutantCode='654' then Strength else null end)*1000,3) methylcyclopentane
  ,ROUND(max(case when  PollutantCode='615' then Strength else null end)*1000,3) methylheptane
 ,ROUND(max(case when  PollutantCode='716' then Strength else null end)*1000,3) methylhexane
  ,ROUND(max(case when  PollutantCode='611' then Strength else null end)*1000,3) trimethylpentane
   ,ROUND(max(case when  PollutantCode='632' then Strength else null end)*1000,3) undecane
  FROM 
 [DB_Air_Product].[PS].[T_Bas_GasRealTimeData]
  --INSERTED--刚刚插入的值
  where monitortime>=(select DATEADD(MINUTE,1,max(MONIDATE)) from [AirDB_YiZhuang].[dbo].[T_Data_VocMin]) 
  --[AirDB_YiZhuang].[dbo].[T_Data_VocMin]最新的时间
  --between '2016-09-23 08:55:00.000' and '2016-09-23 09:01:00.000'
  and 
  --monitortime <(select substring(convert(varchar,max(monitortime),120),0,17)+':00' from [DB_Air_Product].[PS].[T_Bas_GasRealTimeData])
  --[DB_Air_Product].[PS].[T_Bas_GasRealTimeData]最新的时间的分钟值 
  --当两个站点的分钟数据不同步的时候,数据慢的那个站点的数据就入不了[AirDB_YiZhuang].[dbo].[T_Data_VocMin],缺失了
  monitortime <(substring(convert(varchar,@Mint,120),0,17)+':00')
  group by EntCode,MonitorTime
  ) as a
  order by MONIDATE desc


END






GO