0

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?

2
  • 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? Aug 15, 2013 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. Aug 15, 2013 at 2:39

1 Answer 1

9
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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