僅當值在查找表中時才觸發更新

如果在 TagDescLookup 表中找到事件描述，我只想更新評論。如果標記不在查找表中，則不要更改評論列的現有值。目前觸發器將使用正確的值更新註釋列，除非在查找表中找不到該值，它將註釋設置為 Null。我正在使用 SQL Server 2017。

USE [AwxLogger]
GO

/****** Object:  Trigger [dbo].[TagDescTrigger]    Script Date: 14/03/2019 16:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TagDescTrigger]
   ON [dbo].[EventLog]
   AFTER INSERT
   AS
      BEGIN
          SET NOCOUNT ON
             UPDATE [dbo].[EventLog]
       SET Comment =
                 (
           SELECT [Description] FROM TagDescLookup
           WHERE Tag = I.NameOfParameter
           )
       FROM [dbo].[EventLog] AS E
       JOIN INSERTED AS I
             ON E.NameOfParameter = I.NameOfParameter
      END

TagDescLookup桌子

ID int NOT NULL PRIMARY KEY IDENTITY (1,1)  
Tag nvarchar(MAX) NOT NULL  
Description nvarchar(MAX)  

我添加了第二個 where 條件（WHERE E.ConditionName NOT like ‘%log%’）以排除我不想更新的項目。到目前為止已經過測試並且執行良好……感謝其他建議和幫助

       (
       SELECT [Description] FROM TagDescLookup
       WHERE Tag = I.NameOfParameter AND Description IS NOT NULL 
       )
   FROM [dbo].[EventLog] AS E
   JOIN INSERTED AS I  
   ON E.NameOfParameter = I.NameOfParameter WHERE E.ConditionName NOT like '%log%' 
END

您可以將觸發器更改為僅在NameOfParameter匹配Tagfrom時更新TagDescLookup

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TagDescTrigger]
   ON [dbo].[EventLog]
   AFTER INSERT
   AS
      BEGIN
      SET NOCOUNT ON

       UPDATE E
       SET E.Comment =TDL.[Description]
       FROM
       [dbo].[EventLog] AS E
       JOIN INSERTED AS I
       ON E.NameOfParameter = I.NameOfParameter
       JOIN TagDescLookup AS TDL
       ON TDL.Tag = I.NameOfParameter;
       END
   END

你真的需要注意重複Tag的。

---

測試

CREATE TABLE  TagDescLookup 
(ID int NOT NULL PRIMARY KEY IDENTITY (1,1)  ,
Tag nvarchar(MAX) NOT NULL  ,
Description nvarchar(MAX)  );

CREATE TABLE [dbo].[EventLog]
(Comment  nvarchar(MAX),
NameOfParameter nvarchar(max));

範例 1

 INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
 VALUES('bla','bla');

 select * from dbo.EventLog;

結果1

Comment NameOfParameter
bla bla

範例 2

 Insert into TagDescLookup(Tag,Description) 
 VALUES('TaggedVal','NewDescription');


 INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
 VALUES('OldDescription?','TaggedVal');


 SELECT * From dbo.EventLog;

結果2

Comment NameOfParameter
bla bla
NewDescription  TaggedVal

引用自：https://dba.stackexchange.com/questions/232630