Reputation: 559
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