SQL Server – Creating Trigger After Update Excluding a Column

sql servertriggerupdate

I want to create a trigger that set a column to to_sync true if the other columns has been changed.

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync) 
      BEGIN
          UPDATE dbo.product 
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END

the above code raise an error:

Msg 156, Level 15, State 1, Procedure mat_trig, Line 4
Incorrect syntax near the keyword 'SET'.
Msg 140, Level 15, State 1, Line 1
Can only use IF UPDATE within a CREATE TRIGGER statement.
Time: 0.504s

Best Answer

I believe your code is missing or is typo'ed

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
AS -- you miss this 'as'
BEGIN -- you miss this 'begin'
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync) 
      BEGIN
          UPDATE dbo.product 
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END
Related Question