SQL Server trigger on specific column updated

Question

I have a table with the following columns:

ID | CLIENT_SYNCED_TIME  |  NAME  | DESCRIPTION | LM_TIME

The LM_TIME column will be set automatically by a trigger when any of the other column values get updated.

However I want the LM_TIME .... NOT to get updated by the trigger when the CLIENT_SYNCED_TIME column is updated.

I am using the below trigger right now, which updates the LM_TIME when any of the column value is changed.

Simply I just want to make the trigger not to worry about CLIENT_SYNCED_TIME column. What modifications I have to make to achieve this effect?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[updateLM_TIME]
ON [dbo].[INSTITUTIONS]
AFTER INSERT, UPDATE 
AS
    UPDATE dbo.INSTITUTIONS
    SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
    WHERE ID IN (SELECT DISTINCT ID FROM Inserted)
GO

Answer 1

try this.

USE [lms_db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[updateLM_TIME]
ON [dbo].[INSTITUTIONS]

AFTER INSERT, UPDATE
AS
    IF UPDATE(ID) or UPDATE(NAME) or UPDATE(DESCRIPTION)
    BEGIN
        UPDATE dbo.INSTITUTIONS
        SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
        WHERE ID IN (SELECT DISTINCT ID FROM Inserted)
    END

Or

AFTER INSERT, UPDATE
AS
    IF UPDATE(CLIENT_SYNCED_TIME)
        PRINT 'Not Updated';
    ELSE
    BEGIN
        UPDATE dbo.INSTITUTIONS
        SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
        WHERE ID IN (SELECT DISTINCT ID FROM Inserted)
        PRINT 'Updated';
    END