SQL Server trigger on specific column updated

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

thanks to Mr. Bhosale

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 NOT UPDATE(CLIENT_SYNCED_TIME)

BEGIN

UPDATE dbo.INSTITUTIONS
SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
WHERE ID IN (SELECT DISTINCT ID FROM Inserted)

END

Advertisement

Answer