问在遍历列时使用已删除的值

EN

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

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 < @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 @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