Distributing triggers across database tables

tbode2020 · January 21, 2021, 6:24pm

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

ScottPletcher · January 21, 2021, 7:30pm

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.

mike01 · January 21, 2021, 9:24pm

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

tbode2020 · January 22, 2021, 3:30am

Scott, please provide me with more details.
Thanks, Thomas

ScottPletcher · January 22, 2021, 4:24am

Here is the code that creates the base proc:

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'

ScottPletcher · January 22, 2021, 4:26am

Once the proc is created, you can run it from any db and it will run in the context of that db.

For example:
EXEC mim5.dbo.sp_DashboardTriggers
EXEC mim6.dbo.sp_DashboardTriggers
--OR
USE mim7
EXEC dbo.sp_DashboardTriggers

Note that the proc name must begin with sp_ for this technique to work.

tbode2020 · January 22, 2021, 2:35pm

Can you elaborate on the "IF EXISTS(SELECT...." condition?

tbode2020 · January 22, 2021, 5:45pm

sp_issue
My stored procedure is not getting created in the system area on my main server, but was created correctly in my VM test server. Any idea why?

ScottPletcher · January 22, 2021, 5:48pm

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

ScottPletcher · January 22, 2021, 5:49pm

You have to mark the proc as a system object, like so:

EXEC master.sys.sp_MS_marksystemobject 'dbo.sp_DashboardTriggers'

tbode2020 · January 22, 2021, 6:48pm

Thanks for the quick reply. Everything is working as I need now.

tbode2020 · January 22, 2021, 9:13pm

SET @DB = DB_NAME();
Will you explain to me how this works to return the database name?

ScottPletcher · January 22, 2021, 10:24pm

Sure.

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.

ScottPletcher · January 22, 2021, 10:28pm

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

ScottPletcher · January 22, 2021, 10:30pm

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.