CODEWITHSUNDEEP
sqlsql-serverdatabaseperformancet-sql
Christopher Townsend
Christopher Townsend

Reputation: 1746

More efficient way of logging changes in my table trigger

Currently my triggers for each table are like this for every field in a table:

ALTER TRIGGER [dbo].[trg_Statement] ON [dbo].[tbl_Statement]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT  
          'Statement', CU.id, 'id', deleted.id,inserted.id
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.id,'') <> IsNull(deleted.id,'')

    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT 
          'Statement', CU.id, 'idAccount', deleted.idAccount,inserted.idAccount
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.idAccount,'') <> IsNull(deleted.idAccount,'')

    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT 
          'Statement', CU.id, 'OpeningBalance', deleted.OpeningBalance,inserted.OpeningBalance
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.OpeningBalance,'') <> IsNull(deleted.OpeningBalance,'')
   ...

This however is very costly, especially when there are a lot of fields, can anyone propose a more efficient way to track changes to our changelog?

Upvotes: 1

Views: 184

Answers (3)

Laurence R. Ugalde
Laurence R. Ugalde

Reputation: 182

I think the method described in Log Trigger is shorter, faster and clearer. It does not store "types" of changes, such insertions, updates or deletions, instead, it creates an structure called Tuple versioning. Insertions are rows with no previous "version", deletions are rows with no subsecuent "version", intermediate rows are updates.

It has the advantage that it is easier and much more natural to retrieve the data of a particular entity at a certain point of time.

In fact it is easy to get how was the entire table in an specific point of time. It is useful if the table becomes messy and it is required to restore the data from a given "safe" point of time.

Upvotes: 0

Brandon Johnson
Brandon Johnson

Reputation: 355

I agree with Madison, a single row in you AUDIT table that contains the entire record is going to be more useful and more performant. This becomes more true the wider the base table is. In your case:

CREATE TRIGGER IUD_Statement_Audit
ON dbo.tbl_Statement AFTER INSERT, UPDATE, DELETE
AS BEGIN

  IF (@@rowCount = 0) RETURN;

  SET NOCOUNT ON;

  INSERT INTO dbo.tbl_Statement_AUDIT
    (id, idAccount, OpeningBalance, insertedOrDeleted, modTime, modId)
  SELECT id, idAccount, OpeningBalance, 'D', GETDATE(), USER_NAME() FROM DELETED
  UNION ALL
  SELECT id, idAccount, OpeningBalance, 'I', GETDATE(), USER_NAME() FROM Inserted

END

There are many benefits to this approach including: easy join on id column to see all changes and triggers for all tables become copy/paste exercises. In general RDBMS will perform better with a wide tables(one record containing all columns) and fewer records as opposed to a skinny[?] table(one record for each column change). Your current approach will create three records for every record that is inserted, deleted, and updated.

Rolling back to a past point can be done by ordering the audit table by modTime desc, insertedOrDeleted desc and performing the inverse operation... going back as far as you want.

Upvotes: 2

Madison
Madison

Reputation: 411

I sometimes create an identical table to the table I want to log except I add a new identity primary key and a datetime field to track when the change occurred. Then whenever the original table changes, I insert into the tracking table the entire row that changed plus the current datetime.

The pros to this are it is easy to implement and allows rolling back to a previous set of values if needed. It also allows easy joining to current rows if you want to see total history (remember to index if you do this a lot). Also, in your case it would just be a single insert with no logic other than grabbing the correct row. The downside is it stores all the fields every time anything changes and you have to maintain two tables.

You could obviously reduce the fields you didn't want to track or modify this in other ways more custom to your needs. For example, you might want to keep an extra field in the tracking table if the row gets deleted.

In order to see the old value and new value, you'd need to just look backwards in the table by the date to see when it changed.

Upvotes: 1

Related Questions