SQLTeam.com | Weblogs | Forums

Passing a string value when creating a trigger

/*
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'.

Don't include the db name in the CREATE TRIGGER. You're already in that db, guaranteed. And I don't think a trigger name is allowed to reference a db name.

1 Like

The issue appears to be with the value 'PERSONS' syntax. I have used this format successfully for other master trigger creations. I have two versions above. The one which I created in the named database, which works with 'PERSONS' in VALUE and the other which will create the trigger in a named database where 'PERSONS' in VALUE causes the error.

Typo?

Oh right, you need to use extra quotes around 'PERSONS' since it's already within quotes:

= '...
VALUES (''PERSONS'',@recid,GETDATE(),0)
...'

What about stackoverflow, didnt help? xd