CODEWITHSUNDEEP
sqlsql-serverstored-procedurestriggersdynamic-sql
Smolli
Smolli

Reputation:

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

Upvotes: 0

Views: 4879

Answers (3)

Vishantha Peiris
Vishantha Peiris

Reputation: 101

No need to "Alter Login .." use this way for dynamic db and dynamic trigger

set @db = 'XXX'
set @sql = 'use [' + @db + ']; 

exec (''CREATE TRIGGER [dbo].[Trigger1] ON dbo.Table1
    FOR UPDATE, INSERT, DELETE
    AS
    BEGIN
        SET NOCOUNT ON;
        /*your code*/
    END;'')'

exec (@sql)

Upvotes: 0

Andomar
Andomar

Reputation: 238126

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

Upvotes: 0

gbn
gbn

Reputation: 432331

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

Upvotes: 3

Related Questions