Have Geek Brain

Creating a dynamic trigger

I am using MS SQL 2008 and I want to create a trigger on a database that is dynamically created.

Database creation is called inside a stored procedure of another database and it works fine, but when I want to add a trigger or stored procedure, the execution fails.

If I try to run dynamic SQL with

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

I get:

Incorrect syntax next to 'GO'

And if I delete "USE ..." the trigger is created in the wrong database.

Is there a trick to avoid my problems?

thanks

0


a source to share


3 answers


"GO" is not a T-SQL language. It is a keyword that is interpreted by client tools such as SSMS as a packet separator (meaning "send text to the server").

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

If you put "USE dbnname" before EXEC, then it might end up in the default database for the connection. You would have to test (I cannot now, sorry)

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


Or you will need to use sqlcmd or osql to connect and run the code: this allows you to set the db context when connecting. However, not in T-SQL.

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]
')
+3


a source


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

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

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



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

Or create another user who has a default directory in the database where you want to create the trigger and:

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


a source


To create a link just to check for existence and then modify with alter.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MiTrigger]') AND type in (N'TR'))
    EXEC ('CREATE TRIGGER [MiTrigger] ON [dbo].[MiTabla] FOR DELETE AS SET NOCOUNT ON')
GO
0


a source






More articles:


All Articles