Cursor That Executes a Proc that Creates An Audit Trigger

Question

So, I've been battling this thing for a couple of hours and figured I would reach out for a little but of help. I need to create an audit trigger on all tables of a database (This audit trigger works and has been tested) I am just trying to figure out an easy way to deploy vs manually deploying and scripting for each table.

Here is the proc I created that creates the trigger

alter PROCEDURE [dbo].[InsertTRIGGER] 
(@triggerTable varchar(max))

AS 
DECLARE @sql nvarchar(max)
SET @sql = ('

    CREATE TRIGGER [dbo].[TR_@triggertable_AUDIT] ON [dbo].[@triggertable] FOR UPDATE, INSERT, DELETE
AS


DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000),
       @UserGroup VARCHAR(128),



IF IS_MEMBER (''DbEditAudit'') = 1
BEGIN
--You will need to change @TableName to match the table to be audited. 
-- Here we made GUESTS for your example.
SELECT @TableName = ''@triggertable''

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)


-- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = ''U''
       ELSE
               SELECT @Type = ''I''
ELSE
       SELECT @Type = ''D''

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + '' and'', '' on'') 
               + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = ''PRIMARY KEY''
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+''+'','''') 
       + ''''''<'' + COLUMN_NAME 
       + ''=''''+convert(varchar(100),
coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + '')) + ''''>''''''
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = ''PRIMARY KEY''
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
       RAISERROR(''no PK on table %s'', 16, -1, @TableName)
       RETURN
END

SELECT         @field = 0, 
       @maxfield = MAX(ORDINAL_POSITION) 
       FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION > @field
       SELECT @bit = (@field - 1 )% 8 + 1
       SELECT @bit = POWER(2,@bit - 1)
       SELECT @char = ((@field - 1) / 8) + 1
       IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
                                       OR @Type IN (''I'',''D'')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field
               SELECT @sql = ''
insert Audit (    Type, 
               TableName, 
               PK, 
               FieldName, 
               OldValue, 
               NewValue, 
               UpdateDate, 
               UserName)
select '''''' + @Type + '''''',''''''
       + @TableName + '''''','' + @PKSelect
       + '','''''' + @fieldname + ''''''''
       + '',convert(varchar(1000),d.'' + @fieldname + '')''
       + '',convert(varchar(1000),i.'' + @fieldname + '')''
       + '','''''' + @UpdateDate + ''''''''
       + '','''''' + @UserName + ''''''''
       + '' from #ins i full outer join #del d''
       + @PKCols
       + '' where i.'' + ''@fieldname'' + '' <> d.'' + ''@fieldname'' 
       + '' or (i.'' + ''@fieldname'' + '' is null and  d.''
                                + ''@fieldname''
                                + '' is not null)'' 
       + '' or (i.'' + @fieldname + '' is not null and  d.'' 
                                + ''@fieldname''
                                + '' is null)''
               EXEC (@sql)
       END
END
END
')


EXECUTE sp_executesql @sql, N'@triggertable nvarchar(max)', @triggertable = @triggertable

And here is the cursor that I wrote to pull the name of the table to populate the trigger with for each table

DECLARE @table varchar(100)

DECLARE trigger_cursor CURSOR FOR

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME NOT LIKE '%_audit'

OPEN trigger_cursor
FETCH NEXT FROM trigger_cursor INTO @table 

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [InsertTRIGGER] @triggertable = '@table'

FETCH NEXT FROM trigger_cursor INTO @table
END
CLOSE trigger_cursor
DEALLOCATE trigger_cursor

The error that I am getting is this

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'TRIGGER'. Msg 156, Level 15, State 1, Line 23 Incorrect syntax near the keyword 'IF'. Msg 137, Level 15, State 1, Line 27 Must declare the scalar variable "@TableName". Msg 137, Level 15, State 1, Line 30 Must declare the scalar variable "@UserName". Msg 137, Level 15, State 1, Line 37

It actually goes on to list all of my variables but I'll spare your eyes since I've already posted so much.

I'm sure it's something simple, just need a second set of eyes. I believe the issues lies in the very beginning of the proc that creates the trigger or at the very end where i execute the @sql

Answer 1

You're trying to use a variable in places where you can't - as a part of the name of a trigger, and as the name of a table. Instead of passing @triggertable in as a parameter, you may as well just concatenate it directly into the SQL statement, replacing:

@triggertable

with:

' + @triggertable + '

Then, you should get the results you want. Given that you're getting the table names from system tables, you shouldn't actually have to worry about SQL injection here. Do double-check how the cursor returns the table name - in some cases, it may return:

[tablename]

instead of just:

tablename

As noted by Kenneth Fisher, try printing @sql instead of executing it to confirm it looks like what you want (since we've eliminated the parameters you were passing into sp_executesql).