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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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.