实时报警触发器

USE [KS_DataBase]
GO
/****** Object:  Trigger [dbo].[trgAfterUpdateRealAlarm]    Script Date: 06/24/2016 17:27:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: <dlsyaim>
-- Create date: <2016-06-24>
-- Description: <对KS_DataRecordRealAlarm进行判断跟新,就Update，没有Insert>
-- =============================================
ALTER TRIGGER [dbo].[trgAfterUpdateRealAlarm]
   ON  [dbo].[KS_DataRecordReal]
   FOR UPDATE
AS 
BEGIN
  
   -- Insert statements for trigger here
--插入到KS_DataRecordRealAlarm最新报警里面
DECLARE @fStaLowLimitfloat;
DECLARE @fStaHighLimitfloat;
DECLARE @fWorLowLimitfloat;
DECLARE @fWorHighLimitfloat;
DECLARE @fTemLowLimitfloat;
DECLARE @fTemHighLimitfloat;
DECLARE @fCnsLowLimitfloat;
DECLARE @fCnsHighLimitfloat;
DECLARE @fPreLowLimitfloat;
DECLARE @fPreHighLimitfloat;
DECLARE @RamCodevarchar(100);
DECLARE @StaRecInsCumAmtfloat;
DECLARE @WorRecInsCumAmtfloat;
DECLARE @RecPressurefloat;
DECLARE @RecTemperaturefloat;
DECLARE @RecConcentrationfloat;

--SELECT @RamCode=i.RamCode from Inserted i;
--SELECT @StaRecInsCumAmt=i.StaRecInsCumAmt from Inserted i;
--SELECT @WorRecInsCumAmt=i.WorRecInsCumAmt from Inserted i;
--SELECT @RecPressure=i.RecPressure from Inserted i;
--SELECT @RecTemperature=i.RecTemperature from Inserted i;
--SELECT @RecConcentration=i.RecConcentration from Inserted i;

SELECT @RamCode=i.RamCode,@StaRecInsCumAmt=i.StaRecInsCumAmt,
@WorRecInsCumAmt=i.WorRecInsCumAmt,@RecPressure=i.RecPressure,
@RecTemperature=i.RecTemperature,@RecConcentration=i.RecConcentration from Inserted i;


SELECT @fStaLowLimit=StaLowLimit
,@fStaHighLimit=StaHighLimit
,@fWorLowLimit=WorLowLimit
,@fWorHighLimit=WorHighLimit
,@fTemLowLimit=TemLowLimit
,@fTemHighLimit=TemHighLimit
,@fCnsLowLimit=CnsLowLimit
,@fCnsHighLimit=CnsHighLimit
,@fPreLowLimit=PreLowLimit
,@fPreHighLimit=PreHighLimit
FROM [dbo].[KS_AlarmLimit] WHERE RamCode = @RamCode;

--对插入的值进行判断，只要有一个不满足，就写入到KS_DataRecordRealAlarm里面
DECLARE @bStaRecInsCumAmtint;
DECLARE @bWorRecInsCumAmtint;
DECLARE @bRecPressureint;
DECLARE @bRecTemperatureint;
DECLARE @bRecConcentrationint;
DECLARE @bFlagint;
SET @bStaRecInsCumAmt= CASE WHEN (@StaRecInsCumAmt>@fStaLowLimit and @StaRecInsCumAmt<@fStaHighLimit) THEN 1 ELSE 0 END;
SET @bWorRecInsCumAmt= CASE WHEN (@WorRecInsCumAmt>@fWorLowLimit and @WorRecInsCumAmt<@fWorHighLimit) THEN 1 ELSE 0 END;
SET @bRecPressure= CASE WHEN (@RecPressure>@fPreLowLimit and @RecPressure<@fPreHighLimit) THEN 1 ELSE 0 END;
SET @bRecTemperature= CASE WHEN (@RecTemperature>@fTemLowLimit and @RecTemperature<@fTemHighLimit) THEN 1 ELSE 0 END;
SET @bRecConcentration= CASE WHEN (@RecConcentration>@fCnsLowLimit and @RecConcentration<@fCnsHighLimit) THEN 1 ELSE 0 END;
SET @bFlag = @bStaRecInsCumAmt*@bWorRecInsCumAmt*@bRecPressure*@bRecTemperature*@bRecConcentration;

if(@bFlag = 0)
begin
;WITH ChangeAlarmList AS
(SELECT
Inserted.RamCode, 
Inserted.StaRecCumAmt, 
Inserted.StaRecInsCumAmt, 
Inserted.WorRecCumAmt, 
Inserted.WorRecInsCumAmt, 
Inserted.RecPressure, 
Inserted.RecTemperature, 
Inserted.RecConcentration,
Inserted.ColTime, 
Inserted.AddTime, 
Inserted.Status 
FROM Inserted
)
Merge 
Into[dbo].[KS_DataRecordRealAlarm] As Tgt 
Using ChangeAlarmList As Src 
On 
Src.RamCode = Tgt.RamCode

When Matched 
Then 
Update Set 
TGT.RamCode=SRC.RamCode, 
TGT.StaRecCumAmt=SRC.StaRecCumAmt, 
TGT.StaRecInsCumAmt=SRC.StaRecInsCumAmt, 
TGT.WorRecCumAmt=SRC.WorRecCumAmt, 
TGT.WorRecInsCumAmt=SRC.WorRecInsCumAmt, 
TGT.RecPressure=SRC.RecPressure, 
TGT.RecTemperature=SRC.RecTemperature, 
TGT.RecConcentration=SRC.RecConcentration, 
TGT.ColTime=SRC.ColTime, 
TGT.AddTime=SRC.AddTime, 
TGT.Status=SRC.Status

When Not Matched 
Then 
Insert 
( [RamCode]
  ,[StaRecCumAmt]
  ,[StaRecInsCumAmt]
  ,[WorRecCumAmt]
  ,[WorRecInsCumAmt]
  ,[RecPressure]
  ,[RecTemperature]
  ,[RecConcentration]
  ,[ColTime]
  ,[AddTime]
  ,[Status]) 
Values  
( 
SRC.RamCode, 
SRC.StaRecCumAmt, 
SRC.StaRecInsCumAmt, 
SRC.WorRecCumAmt, 
SRC.WorRecInsCumAmt, 
SRC.RecPressure, 
SRC.RecTemperature, 
SRC.RecConcentration,
SRC.ColTime, 
SRC.AddTime, 
SRC.Status 
);
end;




END