How to create a trigger in a different database?

Question

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?

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 02: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. — Andrew Savinykh, Aug 15 '13 at 02:39

Aaron Bertrand · Accepted Answer · 2013-11-11T01:07:24.980

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.

How to create a trigger in a different database?

1 Answers1

Linked