Click here to Skip to main content
15,882,163 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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.
[no name] 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

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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900