/*
Creating a trigger in the database works correctly
*/
ALTER TRIGGER [dbo].[CreatePersonsSyncRecord]
ON [mim15].[dbo].[Persons]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @recid as varchar(50), @isDup as integer
SELECT TOP (1) @recid=[id] FROM Persons ORDER BY update_date_time DESC
SELECT @isDUP = COUNT(GUID) FROM PMtoClinicSync
WHERE table_row_id = @recid and clinic_sync = 0
IF @isDUP = 0
INSERT INTO PMtoClinicSync
(table_name,table_row_id,pm_change_date,clinic_sync)
VALUES ('PERSONS',@recid,GETDATE(),0)
END
/*
Now, trying to make a system stored procedure to create trigger across multiple databases fails
See error message below
*/
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ClinicSyncTriggers] Script Date: 4/9/2021 2:22:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ClinicSyncTriggers]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DB NVARCHAR(50)
DECLARE @TriggerCode NVARCHAR(max);
SET @DB = DB_NAME();
/*
============================PERSONS Trigger======================================
*/
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'PERSONS' AND type IN ('U', 'V'))
BEGIN
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'CreatePersonsSyncRecord' AND type IN ('TR')) DROP TRIGGER [dbo].[CreatePersonsSyncRecord]
SET @TriggerCode =
'CREATE TRIGGER [dbo].[CreatePersonsSyncRecord]
ON ' + @DB + '.[dbo].[PERSONS]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @recid as varchar(50), @isDup as integer
SELECT TOP (1) @recid=[id]
FROM ' + @DB + '.[dbo].PERSONS
RDER BY update_date_time DESC
SELECT @isDUP = COUNT(GUID)
FROM ' + @DB + '.[dbo].PMtoClinicSync
WHERE table_row_id = @recid and clinic_sync = 0
IF @isDUP = 0
INSERT INTO ' + @DB + '.[dbo].PMtoClinicSync
(table_name,table_row_id,pm_change_date,clinic_sync)
VALUES ('PERSONS',@recid,GETDATE(),0)
END';
EXEC(@TriggerCode);
END /IF/
Msg 102, Level 15, State 1, Procedure sp_ClinicSyncTriggers, Line 26 [Batch Start Line 7]
Incorrect syntax near 'PERSONS'.