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
Yes, there is, assuming you're using on-premises db and not Azure.
The easiest way is to create the proc in the master db and mark it as a system proc. You can then run it in the context of any db without needing a "USE" statement.
If you'd be interested in that approach, let me know and I can provide further details.
you can always use the undocumented proc sp_forEachDB. Something like this, also put an if exists drop to avoid issues
Set transaction isolation level read uncommitted
go
Declare @TriggerCode nvarchar(Max) = 'Use [?]
if exists (select 1 from sys.triggers where name = ''RecordNewPatient'')
begin
Drop TRIGGER [dbo].[RecordNewPatient]
end;
exec(''CREATE TRIGGER [dbo].[RecordNewPatient] ON [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 = ''''?'''') = 0
BEGIN
INSERT INTO [mim_common].[dbo].[DashboardMetrics]
([PM_Database],[New_Patient_Registered],[N_P_R_Update])
VALUES (''''?'''',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] = ''''?''''
END
END'')'
exec sp_MsForEachDb @Command1 = @TriggerCode
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_DashboardTriggers
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DB NVARCHAR(50)
DECLARE @TriggerCode NVARCHAR(max);
SET @DB = DB_NAME();
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'Persons' AND type IN ('U', 'V'))
BEGIN
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);
END /*IF*/
RETURN 0;
END
GO
EXEC sys.sp_MS_marksystemobject 'dbo.sp_DashboardTriggers'
The EXISTS verifies that a table (or view) with the name "Persons" exists in the db. If it doesn't exist, there's not much point in trying to create a trigger on that table (or view; technically you can create triggers on certain views).
The sp_ system procs -- like this proc is emulating -- work in only one db at a time, which has to be the current db.
The function DB_NAME() returns the current db name (by default, when you don't provide a db number).
For example, say you exec:
EXEC mim5.dbo.sp_DashboardTriggers
The proc will run in the context of db mim5, as you specified (but without affecting the rest of your script, which will still run in its own db context). Thus, DB_NAME() in the proc will return mim5.
Similarly if you run:
USE mim6
EXEC dbo.sp_DashboardTriggers
the proc will run in the context of mim6, and DB_NAME() in the proc will return mim6.
The ability to run directly in another db context without having to issue a "USE db_name" is a big advantage of the technique of putting the proc in the master db.
If I have several dbs to process, all in the context of db1, but I need to run the proc for mim5 and mim6, I can do this:
USE db1
EXEC dbo.proc1 --this execs from db1
EXEC mim5.dbo.sp_DashboardTriggers --this runs in the context of mim5, since you overrode the db name
EXEC mim6.dbo.sp_DashboardTriggers --this runs in the context of mim6
EXEC dbo.proc2 --this execs from db1
Damn it's stupid for a SQL Server site to use /* and */ as formatting/display chars instead of letting them remain undamaged as comments in SQL Server code.
I strongly prefer to put comments in /* and */ but can't in the code above because the editor damages them beyond compression.
