Geek Answers Handbook

Field Tracking Templates

For one of my recent projects, I had to track field changes . Thus, at any time when the user changed the value of the field, this change was recorded to ensure a complete audit of the changes.

In the database, I implemented this as one FieldChanges table with the following fields:

  • Tablename
  • Fieldname
  • RecordId
  • DateOfChange
  • Changedby
  • Intvalue
  • Textvalue
  • DateTimeValue
  • Boolvalue

The sproc save changes to the object determine for each field whether it has been changed and whether the record inserts into FieldChanges, if it has: if the type of the changed field is int , it writes it to the IntValue field in the IntValue table, etc.

This means that for any field in any table with any id value, I can query the FieldChanges table to get a list of changes.

This works pretty well, but a little awkwardly. Can anyone who has implemented this functionality offer a better approach and why do they think it is better?

I would be very interested - thanks.

David

+6
sql database design-patterns change-tracking
source share
4 answers

The corporate template for this is to have a change table for each table that you create, to display the image after (and possibly before the image) for all columns. You will need:

  • to create an amendment table.
  • triggers to populate them
  • and maintain the above when the table changes over time.

But for the well installation company, all this should already be in place.

My organization only uses this for the following:

  • Audit for dbas and support to manually determine what happened (using SQL).
  • Enterprise Data Warehouse (SAS) sucks the entire delta from production systems for analysis.

We create different tables if they are necessary for the operating systems themselves.

+1
source share

Triggers

We wrote a GUI (internally called Red Matrix Reloaded) to simplify the creation / management of audit log triggers.

Here is some DDL of the material used:

AuditLog Table

 CREATE TABLE [AuditLog] ( [AuditLogID] [int] IDENTITY (1, 1) NOT NULL , [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()), [RowGUID] [uniqueidentifier] NOT NULL , [ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UserGUID] [uniqueidentifier] NULL , [TagGUID] [uniqueidentifier] NULL , [Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

Log Entry Trigger

 CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes FOR INSERT AS /* Load the saved context info UserGUID */ DECLARE @SavedUserGUID uniqueidentifier SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier) FROM master.dbo.sysprocesses WHERE spid = @@SPID DECLARE @NullGUID uniqueidentifier SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}' IF @SavedUserGUID = @NullGUID BEGIN SET @SavedUserGUID = NULL END /*We dont' log individual field changes Old/New because the row is new. So we only have one record - INSERTED*/ INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), --ChangeDate i.NodeGUID, --RowGUID 'INSERTED', --ChangeType USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, --UserGUID 'Nodes', --TableName '', --FieldName i.ParentNodeGUID, --TagGUID i.Caption, --Tag null, --OldValue null --NewValue FROM Inserted i

Trigger for registering updates

 CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes FOR UPDATE AS /* Load the saved context info UserGUID */ DECLARE @SavedUserGUID uniqueidentifier SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier) FROM master.dbo.sysprocesses WHERE spid = @@SPID DECLARE @NullGUID uniqueidentifier SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}' IF @SavedUserGUID = @NullGUID BEGIN SET @SavedUserGUID = NULL END /* ParentNodeGUID uniqueidentifier */ IF UPDATE (ParentNodeGUID) BEGIN INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), --ChangeDate i.NodeGUID, --RowGUID 'UPDATED', --ChangeType USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, --UserGUID 'Nodes', --TableName 'ParentNodeGUID', --FieldName i.ParentNodeGUID, --TagGUID i.Caption, --Tag d.ParentNodeGUID, --OldValue i.ParentNodeGUID --NewValue FROM Inserted i INNER JOIN Deleted d ON i.NodeGUID = d.NodeGUID WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL) OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL) OR (d.ParentNodeGUID <> i.ParentNodeGUID) END /* Caption varchar(255) */ IF UPDATE (Caption) BEGIN INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), --ChangeDate i.NodeGUID, --RowGUID 'UPDATED', --ChangeType USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, --UserGUID 'Nodes', --TableName 'Caption', --FieldName i.ParentNodeGUID, --TagGUID i.Caption, --Tag d.Caption, --OldValue i.Caption --NewValue FROM Inserted i INNER JOIN Deleted d ON i.NodeGUID = d.NodeGUID WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL) OR (d.Caption IS NOT NULL AND i.Caption IS NULL) OR (d.Caption <> i.Caption) END ... /* ImageGUID uniqueidentifier */ IF UPDATE (ImageGUID) BEGIN INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue, NewValue) SELECT getdate(), --ChangeDate i.NodeGUID, --RowGUID 'UPDATED', --ChangeType USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, --UserGUID 'Nodes', --TableName 'ImageGUID', --FieldName i.ParentNodeGUID, --TagGUID i.Caption, --Tag (SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue (SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value FROM Inserted i INNER JOIN Deleted d ON i.NodeGUID = d.NodeGUID WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL) OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL) OR (d.ImageGUID <> i.ImageGUID) END

Trigger to register Delete

 CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes FOR DELETE AS /* Load the saved context info UserGUID */ DECLARE @SavedUserGUID uniqueidentifier SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier) FROM master.dbo.sysprocesses WHERE spid = @@SPID DECLARE @NullGUID uniqueidentifier SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}' IF @SavedUserGUID = @NullGUID BEGIN SET @SavedUserGUID = NULL END /*We dont' log individual field changes Old/New because the row is new. So we only have one record - DELETED*/ INSERT INTO AuditLog( ChangeDate, RowGUID, ChangeType, Username, HostName, AppName, UserGUID, TableName, FieldName, TagGUID, Tag, OldValue,NewValue) SELECT getdate(), --ChangeDate d.NodeGUID, --RowGUID 'DELETED', --ChangeType USER_NAME(), HOST_NAME(), APP_NAME(), @SavedUserGUID, --UserGUID 'Nodes', --TableName '', --FieldName d.ParentNodeGUID, --TagGUID d.Caption, --Tag null, --OldValue null --NewValue FROM Deleted d

And to find out which user in the software made the update, each connection "logs onto SQL Server" by calling a stored procedure:

 CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS /* Saves the given UserGUID as the session "Context Information" */ IF @UserGUID IS NULL BEGIN PRINT 'Emptying CONTEXT_INFO because of null @UserGUID' DECLARE @BinVar varbinary(128) SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) ) SET CONTEXT_INFO @BinVar RETURN 0 END DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16)) SET CONTEXT_INFO @UserGUIDBinary /* To load the guid back DECLARE @SavedUserGUID uniqueidentifier SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier) FROM master.dbo.sysprocesses WHERE spid = @@SPID select @SavedUserGUID AS UserGUID */

Notes

  • Stackoverflow code format removes most blank lines - so formatting sucks
  • We use a user table, not integrated security.
  • This code is provided as a confidence - criticism of our design choice is not allowed. Purists may insist that all registration code must be executed at the business level - they can come here and write / save it for us.
  • blobs cannot be registered using triggers in SQL Server (there is no version before the blog version - there is only what it is). Text and nText are blobs, which makes notes unplayable or makes them varchar (2000).
  • The Tag column is used as arbitrary text to identify the row (for example, if the client has been deleted, the tag will display General Motors North America in the audit log table.
  • TagGUID is used to indicate the string "parent". For example, an InvoiceLineItems entry points to an InvoiceHeader . This way, anyone looking for audit log entries associated with a particular account will find deleted “positions” in the TagGUID tag of the position in the audit log.

  • sometimes the values ​​of "OldValue" and "NewValue" are written as a subselection - to get a meaningful string. i.e ".

    OldValue: {233d-ad34234 ..} NewValue: {883-sdf34 ...}

less useful in the audit trail than:

 OldValue: Daimler Chrysler NewValue: Cerberus Capital Management

Final note . Feel free to do what we do. This is great for us, but everyone else may not use it.

+6
source share

I solve this by version control. One version - one row of the table. The latest version is a string with the most recent last updated date.

0
source share

Just create a trigger and let the trigger automatically track changes and write them to your audit table.

0
source share

More articles:


All Articles