How to create a trigger in a different database? 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?
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.