触发器(十一)Trigger_Insert_AIRQUALITYMinm

USE [AirDB_YiZhuang]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_AIRQUALITYMinm]    Script Date: 2017/12/26 8:36:25 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: pyy
-- Create date: 2016-9-23
-- Description: [PS].[T_Bas_GasRealTimeData]表中插入数据时，向[AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin]中插入数据
-- mondify date: 2017-3-17
-- mondify Description: 触发太频繁（每分钟）影响了源数据[PS].[T_Bas_GasRealTimeData]的实时接入改成[dbo].[T_Mid_AWSHour]（每小时）
---- mondify Description: 2017-4-5站点“宾士国际”'130300001'的数据只更新到了3-28
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_AIRQUALITYMinm]--创建的触发器名称
   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=@t2
--(select case when @t1<@t2 then @t1 else @t2 end)


insert into [AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin](STATIONCODE,MONIDATE,SO2,CO,O3,PM25,PM10,NO2,
TEMPERATURE,AIRPRESSURE,RELATIVEHUMIDITY,WINDSPEED,WINDIRECTION)
--top 13
select  case a.STATIONCODE
when '130300002' then '500000001'--编号写错了5000001
  when '130300001' then '500000002'--5000002
  else null end STATIONCODE,MONIDATE,SO2,CO,O3,PM25,PM10,NO2,
TEMPERATURE,AIRPRESSURE,RELATIVEHUMIDITY,WINDSPEED,WINDIRECTION  
  from (SELECT 
EntCode STATIONCODE,MonitorTime MONIDATE
 ,ROUND(max(case when  PollutantCode='101' then Strength else null end)*1000,0)  SO2
 ,ROUND(max(case when  PollutantCode='106' then Strength else null end),3) CO
 ,ROUND(max(case when  PollutantCode='108' then Strength else null end)*1000,0) O3
  ,ROUND(max(case when  PollutantCode='121' then Strength else null end)*1000,0) PM25
 ,ROUND(max(case when  PollutantCode='107' then Strength else null end)*1000,0) PM10
  ,ROUND(max(case when  PollutantCode='141' then Strength else null end)*1000,0) NO2
   ,ROUND(max(case when  PollutantCode='126' then Strength else null end),0) TEMPERATURE
     ,ROUND(max(case when  PollutantCode='127' then Strength else null end),0) AIRPRESSURE
   ,ROUND(max(case when  PollutantCode='128' then Strength else null end),0) RELATIVEHUMIDITY
     ,ROUND(max(case when  PollutantCode='129' then Strength else null end),0) WINDSPEED
 ,ROUND(max(case when  PollutantCode='130' then Strength else null end),0) WINDIRECTION
  FROM 
 [DB_Air_Product].[PS].[T_Bas_GasRealTimeData]
  --INSERTED--刚刚插入的值
  where monitortime>=(select DATEADD(MINUTE,1,max(MONIDATE)) from [AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin]) 
  --[AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin]最新的时间
  --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_MID_AIRQUALITYMin],缺失了
  monitortime <(substring(convert(varchar,@Mint,120),0,17)+':00')
  group by EntCode,MonitorTime
  ) as a
  order by MONIDATE desc


END


GO