Search code examples
sqlsql-serverstored-procedurestriggersdynamic-sql

Creating a trigger dynamic

I use MS SQL 2008 and I want to create a trigger in a database that is created dynamic.

Creating the database is called within a stored procedure of an other database and runs perfectly, but when I want to add a trigger or a stored procedure, the executing fails.

If I try to run the dynamiy SQL with an

EXEC('USE dbase
GO
CREATE TRIGGER [blah]
GO')

I get:

Wrong syntax near 'GO'

And if I remove the 'USE ...' the trigger will be created in the wrong database.

Is there a trick to avoid my problems?

Thx

Solution

  • "GO" is not T-SQL language. It's a keyword interpreted by client tools like SSMS as a batch separator (that means "send text to server").

    Now, CREATE TRIGGER must be the first statement in the batch so the "USE dbname" can not used.

    If you mention "USE dbnname" before the EXEC, then it may up in the default database for the connection. You'd have to test (I can't right now, sorry)

    --Might work
USE dbase
EXEC ('CREATE TRIGGER [blah]
')

    Or you'll have to use sqlcmd or osql to connect and run the code: this allows you to set the db context on connection. Not within T-SQL though.

    Or you can try ALTER LOGIN xxx WITh DEFAULT_DATABASE = dbname before EXEC

    ALTER LOGIN xxx WITH DEFAULT_DATABASE = dbname 
--Now the EXEC will connect to default db if above approach fails
EXEC('CREATE TRIGGER [blah]
')