I have a SQL Server instance with 50 identical databases. I would like to add the same triggers to the same tables in each database. I have written a dynamic SQL procedure that takes the database name as the only input. I get the following error upon execution:
Msg 2108, Level 15, State 1, Procedure RecordNewPatient, Line 1 [Batch Start Line 2]
Cannot create trigger on 'mim5.dbo.Persons' as the target is not in the current database.
If I run the CREATE TRIGGER by itself and add a USE [dbname] at the top, the trigger gets created in the correct database/table.
Is there a way to do what I want to achieve?
CREATE PROCEDURE DashboardTriggers
@DB varchar(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TriggerCode NVARCHAR(max);
SET @TriggerCode =
'CREATE TRIGGER [dbo].[RecordNewPatient] ON ' + @DB + '.[dbo].[Persons]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT COUNT(PM_Database) from [mim_common].[dbo].[DashboardMetrics]
WHERE PM_Database = '''+@DB+''') = 0
BEGIN
INSERT INTO [mim_common].[dbo].[DashboardMetrics]
([PM_Database],[New_Patient_Registered],[N_P_R_Update])
VALUES ('''+@DB+''',1,GETDATE())
END
ELSE
BEGIN
UPDATE [mim_common].[dbo].[DashboardMetrics]
SET [New_Patient_Registered] =
(SELECT COUNT([id]) FROM [dbo].[Persons]
WHERE [entry_date_time] BETWEEN DATEADD(hour,-24,GETDATE()) AND GETDATE())
,[N_P_R_Update] = GETDATE()
WHERE [PM_Database] = '''+@DB+'''
END
END';
EXEC(@TriggerCode);
RETURN 0;
END