Click here to Skip to main content
14,634,077 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am creating a procedure for creating triggers. I set the codes for creating triggers in a variable. I've used Double Single Quotes for some codes that has single quotes.

These are the errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TRIGGER'.
Msg 102, Level 15, State 1, Line 107
Incorrect syntax near 'QUO'.


Here is the code of procedure:

USE [RCMSDB]
GO
/****** Object:  StoredProcedure [dbo].[CreateAuditTrigger]    Script Date: 5/29/2020 7:51:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Jemanoid
-- Create date: 05-08-2020
-- Description:	Wawawiwow
-- =============================================
ALTER PROCEDURE [dbo].[CreateAuditTrigger] 
	@TableName nvarchar(50),
	@PrimaryKeyId nvarchar(50)
AS
	BEGIN

	DECLARE @sqlTrigger1 nvarchar(max);

	SET @sqlTrigger1 = 'CREATE TRIGGER trg_Audit_' + @TableName + ' 
			ON ' + @TableName + '
			FOR INSERT, UPDATE, DELETE
			AS
			BEGIN
			SET NOCOUNT ON;

			DECLARE @sql VARCHAR(5000)
         ,@sqlInserted NVARCHAR(500)
         ,@sqlDeleted NVARCHAR(500)
         ,@NewValue NVARCHAR(100)
         ,@OldValue NVARCHAR(100)
         ,@UpdatedBy VARCHAR(50)
         ,@ParmDefinitionD NVARCHAR(500)
         ,@ParmDefinitionI NVARCHAR(500)
         ,@TABLE_NAME VARCHAR(100)
         ,@COLUMN_NAME VARCHAR(100)
         ,@modifiedColumnsList NVARCHAR(4000)
         ,@ColumnListItem NVARCHAR(500)
         ,@Pos INT
         ,@RecordPk VARCHAR(50)
         ,@RecordPkName VARCHAR(50)
		 ,@RecordPKDelete VARCHAR(50);

			SELECT * INTO #deleted FROM deleted;
			SELECT * INTO #Inserted FROM inserted;;
	
	SET @TABLE_NAME = ' + @TableName + ';

	SELECT @UpdatedBy = @@SERVERNAME;
  	SELECT @RecordPk = @PrimaryKeyId FROM inserted;
	SELECT @RecordPkDelete = @PrimaryKeyId FROM deleted;


	SET @RecordPkName = ''Id'';
	SET @modifiedColumnsList = STUFF((SELECT '','' + name FROM sys.columns
    WHERE object_id = OBJECT_ID(@TABLE_NAME) AND SUBSTRING(COLUMNS_UPDATED(),
    ((column_id - 1) / 8 + 1), 1) & (POWER(2, ((column_id - 1) % 8 + 1) - 1)) 
	= POWER(2, (column_id - 1) % 8) FOR XML PATH ('''')), 1, 1, '''');

	BEGIN
			DECLARE @Action as char(6);
			SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN ''UPDATE''
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN ''INSERT''
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN ''DELETE''
                        ELSE NULL   
                    END);

	  IF EXISTS(SELECT * FROM DELETED)
		INSERT INTO [dbo].[AuditDataChanges]
                                ([TableName]
                                ,[RecordPK]
								,[Action]
                                ,[UpdatedBy])
                                VALUES
                                (@TABLE_NAME
                                ,CAST(@RecordPKDelete as int)
								,@Action
                                ,@UpdatedBy)
	END;

	WHILE LEN(@modifiedColumnsList) > 0
	BEGIN
		SET @Pos = CHARINDEX('','', @modifiedColumnsList);
		IF @Pos = 0
		BEGIN
		SET @ColumnListItem = @modifiedColumnsList;
		END;
		ELSE
		BEGIN
		SET @ColumnListItem = SUBSTRING(@modifiedColumnsList, 1,
		@Pos - 1);
		END;

		SET @COLUMN_NAME = @ColumnListItem;
		SET @ParmDefinitionD = N''@OldValueOut NVARCHAR(100) OUTPUT'';
		SET @ParmDefinitionI = N''@NewValueOut NVARCHAR(100) OUTPUT'';
		SET @sqlDeleted = N''SELECT @OldValueOut='' + @COLUMN_NAME
		+ '' FROM #deleted where '' + @RecordPkName + ''=''
		+ CONVERT(VARCHAR(50), @RecordPk);
		SET @sqlInserted = N''SELECT @NewValueOut='' + @COLUMN_NAME
		+ '' FROM #Inserted where '' + @RecordPkName + ''=''
		+ CONVERT(VARCHAR(50), @RecordPk);
		EXECUTE sp_executesql @sqlDeleted
                       ,@ParmDefinitionD
                       ,@OldValueOut = @OldValue OUTPUT;
		EXECUTE sp_executesql @sqlInserted
                       ,@ParmDefinitionI
                       ,@NewValueOut = @NewValue OUTPUT;

		IF (LTRIM(RTRIM(@NewValue)) != LTRIM(RTRIM(@OldValue)))
		SET @sql = ''INSERT INTO [dbo].[AuditDataChanges]
                                               ([TableName]
                                               ,[RecordPK]				 
                                               ,[Action]
                                               ,[ColumnName]
                                               ,[OldValue]
                                               ,[NewValue]
                                               ,[UpdatedBy])
                                         VALUES
                                               ('' + QUOTENAME(@TABLE_NAME, '''') + ''
                                               ,'' + QUOTENAME(@RecordPk, '''') + ''
					       ,'' + QUOTENAME(@Action, '''') + ''
                                               ,'' + QUOTENAME(@COLUMN_NAME, '''') + ''
                                               ,'' + QUOTENAME(@OldValue, '''') + ''
                                               ,'' + QUOTENAME(@NewValue, '''') + ''
                                               ,'' + QUOTENAME(@UpdatedBy, '''') + '')'';
		EXEC (@sql);

		SET @COLUMN_NAME = '''';
		SET @NewValue = '''';
		SET @OldValue = '''';
		IF @Pos = 0
		BEGIN
			SET @modifiedColumnsList = '''';
		END;
		ELSE
		BEGIN
			SET @modifiedColumnsList = SUBSTRING(@modifiedColumnsList,
			@Pos + 1,
			LEN(@modifiedColumnsList)
			- @Pos);
		END;
  END;

  DROP TABLE #Inserted;
  DROP TABLE #deleted;
  END;';

	EXECUTE sp_executesql @sqlTrigger1, N'@TableName varchar(50), @PrimaryKeyId varchar(50)',
										@TableName,
										@PrimaryKeyId;

	END;


How can I get rid of these errors?

What I have tried:

I have tried creating triggers using this codes without using procedure. It works fine.
Posted
Updated 29-May-20 0:38am
Comments
Tomas Takac 29-May-20 2:25am
   
Print out content of @sqlTrigger1 and try to run it manually, this should give you more info.
Maciej Los 29-May-20 2:48am
   
Try to add SET NOCOUNT ON; right after BEGIN and before DECLARE. And let me know if it helped.
0x01AA 29-May-20 3:28am
   
In the assignement SET @sqlTrigger1 there is no final quote for '
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;


And I'm not sure wheter this needs to be on one line.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think it's this segment here
'...
	SELECT @UpdatedBy = @@SERVERNAME;
  	SELECT @RecordPk = @PrimaryKeyId FROM inserted;
	SELECT @RecordPkDelete = @PrimaryKeyId FROM deleted;
...'
Shouldn't that be
'...	
  	SELECT @UpdatedBy = @@SERVERNAME;
  	SELECT @RecordPk = ' + @PrimaryKeyId + ' FROM inserted;
	SELECT @RecordPkDelete = ' + @PrimaryKeyId + ' FROM deleted;
...'
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100