Creating a trigger dynamic

Question

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

Answer 1

"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]
')

Answer 2

You can switch the database before calling exec, and switch back right after:

use newdb
exec ('CREATE TRIGGER [blah] ...')
use originaldb

Or create a linked server to the right database, with RPC enalbed, and:

EXEC ('CREATE TRIGGER [blah] ...') AT LinkedServerName

Or create a different user that has the Default Catalog in the database where you'd like to create the trigger and:

EXECUTE AS LOGIN = 'UserName'
EXEC ('CREATE TRIGGER [blah] ...')
REVERT