Pradeep Pradeep - 9 days ago 7
SQL Question

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?

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
UPDATE dbo.INSTITUTIONS
SET lm_time = CONVERT(DATETIME, CONVERT(VARCHAR(20), GETDATE(), 120))
WHERE ID IN (SELECT DISTINCT ID FROM Inserted)

GO

Answer

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