Creating a SQL Server trigger from a variable doesn't work

Question

When I run the below code, it runs and prints the SQL output perfectly.

However the last EXEC statement throws an error

Incorrect syntax near the keyword 'TRIGGER'

The code basically strings together some SQL to create a trigger for all existing tables.

When I manually take that PRINT output at the end and execute it in SSMS it works fine, but the EXEC in the code just won't run it.

DECLARE @sql AS NVARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'CREATE TRIGGER [tr_' + table_name +'] ON 
[' + table_schema + '].[' + table_name + '] FOR INSERT, UPDATE, DELETE AS
SELECT 1 GO' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

PRINT @sql; -- output is correct and I can paste this and it works
EXEC sp_executesql @sql -- doesn't work

You could try to replace the GO with a semicolon (surrounded by a space before and after) ` ; ` and see if that works — marc_s
– marc_s, Commented Feb 13, 2015 at 13:57
Ok I'll bite and ask the big white elephant in the room. Why are you adding a trigger to every single table in your database? Triggers should be used very sparingly, like a few in your entire career. — Sean Lange
– Sean Lange, Commented Feb 13, 2015 at 15:15

dario · Accepted Answer · 2015-02-13 13:55:36Z

2

The SQL command to execute has to be a single batch. So, you can't use GO.

You better declare a CURSOR and execute the script for each table.

answered Feb 13, 2015 at 13:55

dario

5,27912 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a SQL Server trigger from a variable doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related