CODEWITHSUNDEEP
sqltriggers
JabberwockyDecompiler
JabberwockyDecompiler

Reputation: 3390

How do I update triggers across multiple databases?

I have a query that I can select the databases from the sys.databases with the triggers that I wish to update. From there I can create a cursor. However when I go into the cursor to update my triggers using a dynamic db name @DatabaseExecuteName that is set to MyDatabaseName.dbo I receive the error ''CREATE/ALTER TRIGGER' does not allow specifying the database name as a prefix to the object name.' Because I am in a cursor I am not able to execute a USE MyDatabaseName ... GO, the GO statement is not allowed inside the CURSOR. I have tried SQLCMD MODE :setvar DatabaseName "MyDatabaseName" with USE [$(DatabaseName)]; to try to set the use database. I feel I am very close however my strength is not SQL queries. I could use some assistance on what I am missing.

Upvotes: 0

Views: 950

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

You can nest EXEC calls so that you can use a USE and then execute a further statement and you don't need to use GO to seperate the batches. This is a complete script to demonstrate the technique:

create database DB1
go
create database DB2
go
use DB2
go
create table T1 (ID int not null)
go
create table T2 (ID int not null)
go
use DB1
go
exec('use DB2; exec(''create trigger T_T on T1 after insert as
                      insert into T2(ID) select i.ID from inserted i'')');
select DB_NAME()
insert into DB2..T1(ID) values (1),(2);
select * from DB2..T2

Which then shows that this connection is still in the DB1 database, but the trigger was successfully created on the T1 table within the DB2 database.

What you have to watch for is getting your quote-escaping correct.

Upvotes: 2

Related Questions