触发器(十四)Trigger_Insert_VocMinPS

最新推荐文章于 2023-12-30 18:46:47 发布

原创最新推荐文章于 2023-12-30 18:46:47 发布 · 224 阅读

0 ·

CC 4.0 BY-SA版权

sqlserver 专栏收录该内容

58 篇文章

订阅专栏

本文介绍了一个具体的SQL触发器实现案例，该触发器用于在特定表插入数据时，同步更新另一表中的聚合数据。通过详细展示触发器的创建过程及内部逻辑，文章提供了如何基于最新数据进行聚合计算并插入目标表的具体方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [AirDB_YiZhuang]
GO

/****** Object: Trigger [dbo].[Trigger_Insert_VocMinPS] Script Date: 2017/12/26 8:39:27 ******/
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站点“宾士国际”'130300001'的数据只更新到了3-28
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_VocMinPS]--创建的触发器名称
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_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