Trigger- dump 'inserted' table to temp table

I need to write changes to a single audit table, with all field names and values concatenated into a single comment field. For example:

UserId=1225,LastName=Evans,FirstName=Stephanie,MiddleInitial=...

Ugly, I know but that's what they want. I have written code to dynamically get the column names for the table, create the above string with the values, but then I discovered that from within the trigger I cannot access the 'inserted' table with dynamic sql.

Any input is appreciated, a better way? I have already spent the better part of 2 days getting the below code written and now it looks like it won't work! I really do not want to literally list all of the column names to build the string for all of the triggers for all of the tables, it will never be maintained as tables evolve.

If it helps... here is my original trigger code that doesn't work because of the reference to 'inserted' within dynamic sql statement (get -Invalid Object Name 'inserted'- error)

CREATE TRIGGER [tgUsersNew] ON [dbo].[tblUsers]
FOR INSERT
AS

declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(200)
declare @col_name nvarchar(40)
declare @strSql nvarchar (500)

--Note: Using the loathsome cursor because a table variable won't work with the 'Fetch' command below...
DECLARE c_cols CURSOR
KEYSET FOR SELECT c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = 'tblUsers'

set @sql1 = ''
set @sql2 = ''
OPEN c_cols

FETCH NEXT FROM c_cols INTO @col_name
WHILE (@@fetch_status <> -1) -- If Fetch did not fail....
BEGIN IF (@@fetch_status <> -2) --If Fetched row not missing....
BEGIN
set @strSql = 'select @strValue = cast(' + @col_Name + ' as nvarchar(40)) from inserted'
exec sp_executesql @strSql, N'@strValue nvarchar(200) out', @sql2 out
if (@sql2 is null) set @sql2 = ''
set @sql1 = @sql1 + '' + @col_name + '=' + @sql2 + ','
print 'sql1= ' + @sql1
END
FETCH NEXT FROM c_cols INTO @col_name
END
CLOSE c_cols
DEALLOCATE c_cols

INSERT INTO tblAuditAdmin
(
TableAltered,
[Action],
Comment
)
VALUES
(
'tblUsers',
'i',
@sql1
)

steal at 2007-9-4 >

All good advice, but seeing as I am required to dump all to the same audit table I need to find the best way (or should I say the least bad way).

What about dumping the 'inserted' and 'deleted' tables to XML strings, passing the XML to an sp, and parsing the XML back into table variables in the sp? Does that sound like a better solution and do-able?

steal at 2007-9-4 >

Great! Could you email your code to yshteyman@hotmail.com?

I greatly appriciate your effort!

Yulia

YuliaS at 2007-9-4 >

I honestly have no idea if this would perform any better than a cursor, but the following could accomplish the task of compiling a dynamic list of column names without the use of a cursor. I know this doesn't answer the addressed problem, but since some of the ideas you posted were helpful to me, I thought I would share some of my own.

Declare @TableName VarChar(256);
Declare @v_ColName VarChar(256);

Set @TableName = 'Pump';
Set @v_ColName = '';

While Exists ( Select cols.Column_Name From information_schema.tables tabs inner join information_schema.columns cols on tabs.Table_Name = cols.table_name where tabs.table_name = @TableName And cols.Column_Name > @v_ColName )
Begin
Select @v_ColName = Min ( cols.Column_Name )
From information_schema.tables tabs
inner join information_schema.columns cols on tabs.Table_Name = cols.table_name
where tabs.table_name = @TableName And cols.Column_Name > @v_ColName;

Print @v_ColName;
End

-Steve

mr.wilson13 at 2007-9-4 >

hi Steal ,

Me too wud like to see the code.

my id mohit_nitdgp@yahoo.co.in

Thanks in advnc.

MOHIT_NITDGP at 2007-9-4 >

I will wait your reply.

Regards.

Julio del Aguila.

JuliodelAguila at 2007-9-4 >