Canox
Canox

Reputation: 559

MSforeachtable does not execute triggers

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

Upvotes: 2

Views: 59

Answers (0)

Related Questions