触发器(十二)Trigger_Insert_PollMinPS

USE [AirDB_YiZhuang]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_PollMinPS]    Script Date: 2017/12/26 8:37:31 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author: pyy
-- Create date: 2017-2-4
-- Description: [PS].[T_Bas_GasRealTimeData]表中插入数据时，向[AirDB_YiZhuang].[dbo].[T_Data_PollMin]中插入数据
-- 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_PollMinPS]--创建的触发器名称
   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_Data_PollMin](STATIONCODE,MONIDATE,NOx,NH3,TNX,H2S)
--top 13
select  case a.STATIONCODE
when '130300002' then '500000001'--编号写错了5000001
  when '130300001' then '500000002'--5000002
  else null end STATIONCODE,MONIDATE,NOx,NH3,TNX,H2S  
  from (SELECT 
EntCode STATIONCODE,MonitorTime MONIDATE
 ,ROUND(max(case when  PollutantCode='102' then Strength else null end)*1000,3)  NOx
     ,ROUND(max(case when  PollutantCode='117' then Strength else null end)*1000,3) NH3
   ,ROUND(max(case when  PollutantCode='118' then Strength else null end)*1000,3) TNX
 ,ROUND(max(case when  PollutantCode='140' then Strength else null end)*1000,3) H2S
  FROM 
 [DB_Air_Product].[PS].[T_Bas_GasRealTimeData]
  --INSERTED--刚刚插入的值
  where monitortime>=(select DATEADD(MINUTE,1,max(MONIDATE)) from [AirDB_YiZhuang].[dbo].[T_Data_PollMin]) 
  --[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