Trigger to update only if value is in lookup table

Question

I only want to update the comment if a description for the event is found in the TagDescLookup Table. If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column. The current trigger will update the comment column with the correct value unless the value isn't found in the lookup table, it sets comment to Null. I'm using 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 Table

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

Randi Vertongen · Answer 1 · 2019-03-20T18:09:58.477

You could change the trigger to only update when the NameOfParameter matches the Tag from 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

You really need to look out for duplicate Tag's.

Testing

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));

Example1

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

  select * from dbo.EventLog;

Result1

Comment NameOfParameter
bla bla

Example2

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


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


  SELECT * From dbo.EventLog;

Result2

Comment NameOfParameter
bla bla
NewDescription  TaggedVal

score 0 · Accepted Answer · answered Apr 08 '19 at 12:02

I added the a second where condition (WHERE E.ConditionName NOT like '%log%') to exclude the items I didn't want updated. Tested and works well so far... Thanks for the other suggestions and help

        (
        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

Trigger to update only if value is in lookup table

2 Answers2