SQLServer 2008 触发器

获得实时信息后，触发插入当日的最大最小值

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,@MinPressureTime datetime,@AveragePressure decimal(10,@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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!