SQLServer 2008 触发器

来源：互联网发布：水墨字体软件编辑：程序博客网时间：2024/06/01 11:51

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

1）打开数据库，打开表-触发器-新建触发器

2）编程

USE [lpp]GO/****** Object:  Trigger [dbo].[Trigger_Building_His]    Script Date: 09/10/2016 12:00:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER TRIGGER [dbo].[Trigger_Building_His]    ON  [dbo].[Building]   AFTER INSERTAS 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 @time2select top 1 @MaxPressureTime=CollectTime from dbo.Building where Pressure=@MaxPressure order by CollectTime descselect top 1 @MinPressureTime=CollectTime from dbo.Building where Pressure=@MinPressure order by CollectTime descselect @AveragePressure=avg(Pressure) from dbo.Building where CollectTime between @time1 and @time2if (select count(*) from dbo.Building_His where StaticDate = @StaticDate)=0begininsert into dbo.Building_His([StaticDate],[MaxPressure],[MaxPressureTime],[MinPressure],[MinPressureTime],[AveragePressure])values(@StaticDate,@MaxPressure,@MaxPressureTime,@MinPressure,@MinPressureTime,@AveragePressure)endelsebeginupdate dbo.Building_Hisset StaticDate=@StaticDate,MaxPressure=@MaxPressure,MaxPressureTime=@MaxPressureTime,MinPressure=@MinPressure,MinPressureTime=@MinPressureTime,AveragePressure=@AveragePressurewhere StaticDate=@StaticDateend--END

完成之后，看一下是ALTER 还是CREATE，如果是创建应为CREATE，点击执行

0 0