获得实时信息后,触发插入当日的最大最小值
1)打开数据库,打开表-触发器-新建触发器
2)编程
USE [lpp]
GO
/****** Object: Trigger [dbo].[Trigger_Building_His] Script Date: 09/10/2016 12:00:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[Trigger_Building_His]
ON [dbo].[Building]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- Insert statements for trigger here
declare @StaticDate nvarchar(10), @MaxPressure decimal(10,1),@MaxPressureTime datetime,
@MinPressure decimal(10,1),@MinPressureTime datetime, @AveragePressure decimal(10,1),
@CollectTime datetime, @time1 datetime, @time2 datetime
select @CollectTime=CollectTime from inserted
set @StaticDate=CONVERT(char(10),@CollectTime,120)
set @time1=convert(datetime,@StaticDate + ' 00:00:00',120)
set @time2=convert(datetime,@StaticDate + ' 23:59:00',120)
select @MaxPressure=Max(Pressure),@MinPressure= Min(Pressure) from dbo.Building where CollectTime between @time1 and @time2
select top 1 @MaxPressureTime=CollectTime from dbo.Building where Pressure=@MaxPressure order by CollectTime desc
select top 1 @MinPressureTime=CollectTime from dbo.Building where Pressure=@MinPressure order by CollectTime desc
select @AveragePressure=avg(Pressure) from dbo.Building where CollectTime between @time1 and @time2
if (select count(*) from dbo.Building_His where StaticDate = @StaticDate)=0
begin
insert into dbo.Building_His([StaticDate],[MaxPressure],[MaxPressureTime],[MinPressure],[MinPressureTime],[AveragePressure])
values(@StaticDate,@MaxPressure,@MaxPressureTime,@MinPressure,@MinPressureTime,@AveragePressure)
end
else
begin
update dbo.Building_His
set StaticDate=@StaticDate,MaxPressure=@MaxPressure,MaxPressureTime=@MaxPressureTime,
MinPressure=@MinPressure,MinPressureTime=@MinPressureTime,AveragePressure=@AveragePressure
where StaticDate=@StaticDate
end
--
END
完成之后,看一下是ALTER 还是CREATE,如果是创建应为CREATE,点击执行