0

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
3

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
0

You were really close. Try this:

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
  AS
    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

I believe you had one too many END statements plus you needed the AS piece on line 4.

Here is another stack overflow answer where someone tried to do something similar.

  • Msg 2714, Level 16, State 2, Procedure product_trig, Line 1 There is already an object named 'product_trig' in the database. Msg 140, Level 15, State 1, Line 1 Can only use IF UPDATE within a CREATE TRIGGER statement. Time: 0.504s – m3asmi Sep 7 '18 at 8:39
  • Based on that error, I would assume you need to change it from a CREATE to an ALTER, jyao also had a viable solution that looks like it met your needs. – Shaulinator Sep 7 '18 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.