2

I have following triggers which I want to execute on each table in my database:

EXECUTE sp_MSforeachtable @command1 = 
  'IF EXISTS (SELECT * FROM sys.triggers 
          WHERE Object_ID = Object_ID("Insert_Serverdate_Into_CreatedAt"))
  DROP TRIGGER Insert_Serverdate_Into_CreatedAt
GO

CREATE TRIGGER Insert_Serverdate_Into_CreatedAt ON ?
AFTER INSERT
AS
BEGIN
INSERT INTO ? (CreatedAt) VALUES(GETDATE())
END
GO

IF EXISTS (SELECT * FROM sys.triggers 
          WHERE Object_ID = Object_ID("Insert_Serverdate_Into_UpdatedAt"))
DROP TRIGGER Insert_Serverdate_Into_UpdatedAt
GO

CREATE TRIGGER Insert_Serverdate_Into_UpdatedAt ON ?
AFTER UPDATE
AS
BEGIN
INSERT INTO ? (UpdatedAt) VALUES(GETDATE())
END
GO'

I'm getting following Errors for each table:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 6
'CREATE TRIGGER' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 25

When I try this triggers without the MSforeachtable procedure just on one table, it works. Does someone know what's wrong with the query above?

Thank you

EDIT:

I even tried to separate both statements into two commands and leave the DROP TRIGGER out. Just creating it if it does not exists. Now I'm getting erros that the Syntax is incorrect near the Keyword TRIGGER

Canox
  • 557
  • 1
  • 7
  • 20
  • `GO` is a client-tools command, not a SQL Server one. It's used to separate batches. Since the entire string passed to `MSforeachtable` is dealt with on the server side, you cannot use `GO` (and thus, cannot have separate batches) – Damien_The_Unbeliever Jun 15 '17 at 05:42
  • So this means I need another solution for this? What's the right way then to execute These batches on each table? – Canox Jun 15 '17 at 05:43
  • @Damien_The_Unbeliever - Then why did we get this error `'CREATE TRIGGER' must be the first statement in a query batch.` – Pரதீப் Jun 15 '17 at 05:47
  • 1
    @Prdp - because the `GO`s aren't being recognized and so the `CREATE TRIGGER` *isn't* the first statement in a batch. The entire string is a single batch. – Damien_The_Unbeliever Jun 15 '17 at 06:01

0 Answers0