触发器(二)Trigger_Insert_VocMin
来源:互联网 发布:淘宝电脑版首页登录 编辑:程序博客网 时间:2024/05/01 13:50
USE [DB_Air_Product]
GO
/****** Object: Trigger [PS].[Trigger_Insert_VocMin] Script Date: 2017/9/15 16:09:36 ******/
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 Description:2017-4-5站点“宾士国际”的数据只更新到了3-28
-- =============================================
ALTER TRIGGER [PS].[Trigger_Insert_VocMin]--创建的触发器名称
ON [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=@t1
--(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