触发器(一)Trigger_Insert_AIRQUALITYMin

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

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

0 ·

CC 4.0 BY-SA版权

sqlserver 专栏收录该内容

58 篇文章

订阅专栏

本文介绍了一个SQL触发器的设计与实现，该触发器用于在一张表中插入数据时，将这些数据同步到另一张表中。具体地，当[PS].[T_Bas_GasRealTimeData]表中有新数据插入时，触发器会收集并处理这些数据，并将其插入到[AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin]表中。

USE [DB_Air_Product]

/****** Object: Trigger [PS].[Trigger_Insert_AIRQUALITYMin] Script Date: 2017/9/15 15:57:51 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author:pyy

-- Create date: 2016-9-23

-- Description:[PS].[T_Bas_GasRealTimeData]表中插入数据时，向[AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYMin]中插入数据

-- =============================================

ALTER TRIGGER [PS].[Trigger_Insert_AIRQUALITYMin]--创建的触发器名称

ON [PS].[T_Bas_GasRealTimeData] --触发器基于的表

AFTER INSERT --触发类型:插入时触发

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_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