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

If there's an `UPDATE` statement and it updates `CLINET_SYNCED_TIME` (and other columns) but happens to set it to the same value it already has, what should happen then? — Damien_The_Unbeliever, Nov 29 '16 at 11:27
It's not a problem sir.. I just want to make the trigger not to worry about "CLIENT_SYNCED_TIME" column — Pradeep, Nov 29 '16 at 11:29
Have a look: http://stackoverflow.com/questions/17768608/what-is-the-practical-use-of-timestamp-column-in-sql-server-with-example — Ivan Starostin, Nov 29 '16 at 11:34

score 9 · Answer 1 · edited Jul 28 '20 at 11:51

9

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

edited Jul 28 '20 at 11:51

sigod

3,514
2
21
44

answered Nov 29 '16 at 11:34

Mr. Bhosale

3,018
1
17
34

sir.. for simplicity I have specified only 4 columns but the table is actually having more than 20 columns.! Is there any way to ignore only a specific column.! – Pradeep Nov 29 '16 at 11:36
1

sir thank u for the help... i've achived this by modifying your code IF NOT UPDATE(client_last_syncd_time) – Pradeep Nov 29 '16 at 11:44
@Pradeep also you can use if else for it. check code once – Mr. Bhosale Nov 29 '16 at 11:50
Wow..! Thanks sir..! – Pradeep Nov 29 '16 at 11:52
Where does the PRINT go, are you able to read it in logs? – Craig P Nov 16 '22 at 18:41

score 2 · Accepted Answer · answered Nov 29 '16 at 11:47

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

SQL Server trigger on specific column updated

2 Answers2