Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides. The databases are on the same server instance. If yes then how?

For example this does not work:

create PROCEDURE [dbo].[CreateTriggrer]
  @db varchar(60)
AS
BEGIN
  SET NOCOUNT ON;   
  declare @statement nvarchar(max) = N'CREATE TRIGGER [dbo].[TestTrigger]
   ON  [$database$].[dbo].[TestTable] 
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
  PRINT ''test''
END'

    set @statement = REPLACE(@statement,'$database$',@db)
    EXEC dbo.sp_executesql @statement = @statement

END

When called like this:

EXEC [dbo].[CreateTriggrer] @db = N'TestDatabase'

It returns this error:

Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 6
Cannot create trigger on 'TestDatabase.dbo.TestTable' as the target is not in the current database.

Which is fair enough. Is there way to achieve what I want?

share|improve this question
    
Do you really need the database to be parameterized? Shouldn't the table be parameterized also? There are ways to nest exec sp_executesql calls so that they occur on the target database, but your code is dumbed down enough that I can't understand what you're really trying to do. Can you elaborate some? –  Aaron Bertrand Aug 15 '13 at 2:35
    
@AaronBertrand, there are 50 databases with the same schema on the same server installed as a part of third party software package. They all have the same structure. I need to create a trigger on the same table in all of them. I need to do it in response on the database restore event, since database restores are frequent thing that the software package does. I can detect the event easily with SQL Alerts and execute a job that executes an SP in response. This part I have no problem with. But I can't figure out how to create a trigger. –  zespri Aug 15 '13 at 2:39

1 Answer 1

up vote 3 down vote accepted
DECLARE @statement NVARCHAR(MAX) = N'CREATE TRIGGER [dbo].[TestTrigger]
  ON [dbo].[TestTable] 
  AFTER INSERT, UPDATE, DELETE
  AS 
  BEGIN
    PRINT ''test'';
  END';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + '.sys.sp_executesql';

EXEC @sql @statement;

This is not entirely intuitive, but it should work. Here is a simpler example that you can test independently:

USE [master];
GO
DECLARE @db SYSNAME = N'tempdb';

DECLARE @statement NVARCHAR(MAX) = N'SELECT DB_NAME(), * FROM sys.database_files';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + 'sys.sp_executesql';

EXEC @sql @statement;

Credit to Erland Sommarskog - you should read this article if you want to understand more deeply how it works.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.