Get the deleted value using while looping through the columns I wrote a trigger to do auditing.  To capture all the changes, I loop through the list of columns.  The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get back the actual field name. I want to get the value, so I don't have to explictly name each field (like I did when I set the NewValue - this works perfectly). Trying to avoid this because, just like most databases, we have tables with several fields. I'm stuck with the syntax on this.  Would like to do something like:

@OldValue = (Select 'd.' + @fieldname from deleted)


Here's my code:

SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

SELECT  @TableName = N'TESTAUDITTABLE'
SELECT  @PKCols =  column_name
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                    INNER JOIN
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                    TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                    and ku.table_name= @TableName
                    ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

SELECT  @UserName = SYSTEM_USER
SELECT  @UpdateDate = {fn now()}

SELECT  @field = 0 
SELECT  @maxfield = MAX(ORDINAL_POSITION) 
        FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

WHILE   @field &lt; @maxfield
BEGIN
       SELECT @field = MIN(ORDINAL_POSITION) 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = @TableName 
               AND ORDINAL_POSITION &gt; @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) &amp; @bit &gt; 0 OR @Type IN ('I','D')
       BEGIN
               SELECT @fieldname = COLUMN_NAME 
                       FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_NAME = @TableName 
                       AND ORDINAL_POSITION = @field

                SELECT @ID = coalesce( i.ORIGREC , d.ORIGREC ) ,
                         @Type = case
                                       when i.ORIGREC is not null and d.ORIGREC is     null then 'Insert' -- insert
                                       when i.ORIGREC is not null and d.ORIGREC is not null then 'Update' -- update
                                       when i.ORIGREC is     null and d.ORIGREC is not null then 'Delete' -- delete
                                     end,
                 @OldValue = (Select @fieldname from deleted), 
                 @NewValue = (Select i.NAME from #ins)

                FROM inserted i
                FULL join deleted d on d.ORIGREC = i.ORIGREC

               SELECT @sql = '
                    INSERT INTO [dbo].[LE_AUDIT] (
                        DB_USER,
                        TableName,
                        ID,
                        FieldName,
                        OldValue,
                        NewValue,
                        CHANGEDATE,
                        TRIG_TYPE)                      
                    VALUES (''' + SYSTEM_USER + ''',''' + @TableName +
                    ''',''' + @ID + ''',''' + @fieldname + ''',''' + 
                    @OldValue + ''',''' + @NewValue + ''',''' +  @UpdateDate  + ''',''' + @Type + ''')' 
               EXEC (@sql)

               print @sql
       END
END


The trigger works fine.  Here is the original source for the link.  https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

I had to modify it to force it to work in conjunction with another app.  Just need to modify the one line to capture the value without specifying the actual field name in the while loop.
I'm short on time so I wasn't able review your code, but I'm basing this answer off your question: How to get the deleted values/capturing changes?

Just yesterday I was reading from CH. 8 of Itzik Ben-Gan's "Microsoft SQL Server 2012 T-SQL Fundamentals" and he had a good section on creating audit tables with the use of the OUTPUT clause. To me this was eye opening and a really cool and simple way of tracking changes such as UPDATES or DELETES.

Assume we have an Orders table with 4 columns: orderid, custid, empid, and orderdate. If we wanted to create an audit table that tracks delete statements the code would be:

DELETE FROM dbo.Orders
OUTPUT
    deleted.orderid,
    deleted.orderdate,
    deleted.empid,
    deleted.custid
Into dbo.OrdersAudit
WHERE orderid = '5';


This code will run your delete statement, and store the all the values into your audit table. The OUTPUT clause can also be used to track your inserts and updates. From what I can tell, the downside is you have to incorporate the OUTPUT clause into all of your procedures. But I wasn't even aware of this clause, and it's super simple to learn the syntax. This probably wasn't what you were looking for, but I was really excited when I read about this clause, and looks like an easy way to track data changes for audits.