Trigger based history

Question

What I am trying to do is find out which fields were updated and record the change to a different table.

DECLARE 
    @BillNo int,
    @column_name varchar(500)  

SELECT @BillNo = BillNo FROM INSERTED
DECLARE HistoryMonitorLoop CURSOR FOR
    SELECT    
        column_name 
    FROM 
        information_schema.columns
    WHERE 
        table_name = 'Shipment';
OPEN HistoryMonitorLoop
FETCH next FROM HistoryMonitorLoop INTO @column_name
WHILE @@Fetch_status = 0
BEGIN
    DECLARE
        @OldValue varchar(500),
        @NewValue varchar(500)
    SET @OldValue = (SELECT @column_name FROM Deleted);
    SET @NewValue = (SELECT @column_name FROM Inserted);
    IF(@OldValue != @NewValue)
    BEGIN
        DECLARE @Comment varchar(5000)
        SELECT @Comment = @column_name + ' Changed from ' + @OldValue + ' to ' + @NewValue
        EXEC ShipmentNote_Insert @BillNo=@BillNo,@CoordinatorID=1,@Comment=@Comment
    END
    FETCH next FROM HistoryMonitorLoop INTO @column_name
END
CLOSE HistoryMonitorLoop
DEALLOCATE HistoryMonitorLoop

what is happening is the
SET @OldValue = (SELECT @column_name FROM Deleted);
SET @NewValue = (SELECT @column_name FROM Inserted);

are setting the @OldValue and @NewValue = to the columnname instead of the value of the column – sql is processing it as SET @OldValue = (SELECT 'column_name' FROM Deleted);

Answer 1

This wont work:

SET @OldValue = (SELECT @column_name FROM Deleted);
SET @NewValue = (SELECT @column_name FROM Inserted);

You're attempting dynamic sql here, which won't work. You have to hard-code the SQL, the variable @column_name will not be dynamically replaced with its value, because the SQL of the trigger gets parsed once, before the trigger runs. With this, you'll (depending on your settings) probably get the literal value of the column name.

It is possible to get dynamic SQL (by connecting to the server in another process, or in MySQl by creating a prepared statement), but it's not possible to do that and reference the "magic" INSERTED and DELETED pseudo-tables available in a trigger.

So your clever use of information_schema.columns won't work. What you can do is leverage that cleverness to write a stored procedure to generate the trigger (this is in fact what I did when I had to write auditing triggers). Then whenever you change the Shipment table, you'll have to run the sp to generate the "create trigger...." statmentnt, and then run that generated statement to re-create the trigger.

Answer 2

What I am trying to do is find out which fields were updated

In SQL Server there are two functions that does exactly what you are looking for.

• Columns_Updated() - Check if one or more column(s) is/are inserted/deleted within trigger
• Update() - Check if a single column is updated within trigger

Answer 3

See this Pop on the Audit Trail It uses a query in a loop as opposed to a cursor, to do just what you're wanting to do.

Answer 4

I'd rethink your whole process. Triggers can be huge performance killers when written improperly. Anytime you think you need to use a cursor or a loop, think again. You need to do this in a set-based fashion.

We use a two table trigger approach. One that records the details about when and who changed the table and a related table that contains the information that was changed. This helps us see all records that were changed at one time. We use an updated statement for each field to populate the second table something like:

if (update([test]))
  begin
    insert [myAudit].dbo.[mytableAuditLogDetail](AuditLogID, ID, ColumnName,   
                                                 OldValue, NewValue)
    select
      @AuditLogID,
      i.[mytableid]),
      'test',
      convert(varchar(8000), d.[test], 0),
      convert(varchar(8000), i.[test], 0)
    from  inserted i
    inner join deleted d on i.[mytableid]=d.[mytableid]
      and (
      (i.[test] <> d.[test]) or 
      (i.[test] is null and d.[test] Is Not Null) or
      (i.[test] is not null and d.[test] Is Null)
          )   
   end

We rebuild the trigger code dynamically every time the schema is changed, but the trigger itself is not dynamic. Our trigger process runs very fast even when we do large imports.