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 @fStaLowLimit float;
DECLARE @fStaHighLimit float;
DECLARE @fWorLowLimit float;
DECLARE @fWorHighLimit float;
DECLARE @fTemLowLimit float;
DECLARE @fTemHighLimit float;
DECLARE @fCnsLowLimit float;
DECLARE @fCnsHighLimit float;
DECLARE @fPreLowLimit float;
DECLARE @fPreHighLimit float;
DECLARE @RamCode varchar(100);
DECLARE @StaRecInsCumAmt float;
DECLARE @WorRecInsCumAmt float;
DECLARE @RecPressure float;
DECLARE @RecTemperature float;
DECLARE @RecConcentration float;
--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 @bStaRecInsCumAmt int;
DECLARE @bWorRecInsCumAmt int;
DECLARE @bRecPressure int;
DECLARE @bRecTemperature int;
DECLARE @bRecConcentration int;
DECLARE @bFlag int;
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
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 @fStaLowLimit float;
DECLARE @fStaHighLimit float;
DECLARE @fWorLowLimit float;
DECLARE @fWorHighLimit float;
DECLARE @fTemLowLimit float;
DECLARE @fTemHighLimit float;
DECLARE @fCnsLowLimit float;
DECLARE @fCnsHighLimit float;
DECLARE @fPreLowLimit float;
DECLARE @fPreHighLimit float;
DECLARE @RamCode varchar(100);
DECLARE @StaRecInsCumAmt float;
DECLARE @WorRecInsCumAmt float;
DECLARE @RecPressure float;
DECLARE @RecTemperature float;
DECLARE @RecConcentration float;
--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 @bStaRecInsCumAmt int;
DECLARE @bWorRecInsCumAmt int;
DECLARE @bRecPressure int;
DECLARE @bRecTemperature int;
DECLARE @bRecConcentration int;
DECLARE @bFlag int;
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