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?
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?