How ro Create an audit trail for records (rows) changed 1

[DougP]

I want to track any changes made to a table, any row and column data.
I am creating the following manually:
I made an update stored procedure. shown is sort-of pseudo code.
I don't need hekp with the code per-se and it may not be 100% syntax

it accepts 6 parmeters. one is for each column name.
Create Procedure ChangeMe
Update myTable
Set Col_1 = @Parm1, Col_2 = @Parm2 .... etc
Where UniqueRecordID = @RecordId passed in parameter 6.

then I wanted to look up the record before it was updated and get the values. I have another table called AuditTrail with these columns
@LastName ,
@FirstName ,
@Tablename,
@ColumnName,
@DataChanged,
@RecordUniqueID,
@DateRecordEdited

I was going to add one record to it based on every column that changed.

your Thoughts, ideas, quicker ways



DougP

 

[djj55]

Are you able to use an update trigger? We do this to do what I think you want.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DougP]

I would like to be able to quickly look at the audit trail table (i.e. a report of some kind) and see who changed what? when. this is time sheet data and if later there are decrepencies we can track/show it.

DougP

 

[Olaf Doschke]

Triggers. Specifically instead of update and instead of delete.

The general idea is you have a tables with current data and identical table, eg with prefix Hist(ory) to store the reecord as it was before it was updated or deleted.

The main code needed in the triggers is to INSERT INTO HistTable SELECT Table.* FROM Table, Inserted Where Table.ID = Inserted.ID (or Deleted.ID)

And don't forget to also to do the Insert or Delete, as instead of triggers are done instead of the inserts or deletes you also insert into the main table, that is you do

INSERT INTO Table Select * From Inserted
or
DELETE FROM Table Where Table.ID In Select ID From Deleted.

You may instead go for any audit trail trigger, eg this one:

http://sqlblog.com/blogs/paul_niels...gen-to-create-fixed-audit-trail-triggers.aspx

Although, besides putting in the names of table/history table the above statements work with any table structure the same way.

Bye, Olaf.

 

[fredericofonseca]

if using version 2008 of SQL it may be better for you to use CDC and process your changes based on that - will only work if your tables already contain the first and last name (or user id) of the user making the change.

It will most likely be faster than triggers

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

That's news to me.

http://www.simple-talk.com/sql/lear...change-data-capture-(cdc)-in-sql-server-2008/

Great feature.

It has some pros and cons, though. Maybe it's a matter of finetuning, but for example I wouldn't log a record as it was before an update in the _CT table, that should already be the last state. It may not, if CDC was disabled in between changes.

I perhaps also wouldn't log the insert, I see the audit table as a log of what a record has been and the current data in the main table, so I'd only log update and delete operations, but not inserts. It's a matter of tastes and of needs.

I like the idea of the update mask and overall it's nice and quite easy to setup, much easier thatn triggers.

On the other side, if that better fits your need, you also find triggers, that only log the condensed information about fields changed, instead of always copying the full record, eg an audit entry contains table, field, oldval, newval. That can be shorter for simple updates and much more verbose, if all fields of a record are really changed.

Logging full records has one big advantage: It's very simple to retrieve a record as it was at a certain time, if the table contains a timestamp field or you add it a least to your audit table. But in many situations and applications only some fields of a table are changed.

I wonder, if it would always be faster, if you eg can't turn off the logging of insert and the beforeupdate vs afterupdate states of records, CDC has to do more than the trigger. But I agree it certainly has a lower level access to the data and can much easier copy a record via internal function, than you can do in triggers via T-SQL. That's also reflected in some of the meta data CDC stores in the audit _CT table:, namely in the lsn (log sequential numbers) logged, which relate this data to the transaction log.

CDC is robust, at least it should be as a built in feature, triggers have to stand the test of time. Thaat's maybe the best argument for CDC.

If you want this data permanent, you should care about the cleanup job, though, as far as I understand this.

Bye, Olaf.

 

[fredericofonseca]

CDC processing its done on the background so quite small impact on the performance of the application (unless the server is under heavy load - but on this case triggers will have an even higher impact on performance) - triggers as they are part of the transaction have an immediate impact on the performance of the application.
With CDC it is also immediately clear which columns changed as the _CT record has a bit field with that info.

Works quite well - and as a audit log of what has changed it is great as long as any additional info required such as user id of change is already stored on each table.

In some of the companies I have this implemented CDC records are archived onto another database daily or weekly for future reference should it be needed. And are also used for managing the loads onto DW´s and other peripheral systems

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

I read a bit into it, and now won't care much, which is faster. Actually take it for granted a built in feature is faster than triggers.

Archiving is a good idea, but you can also drop the cleanup job or set the retention time higher. Anyway, archiving plus cleanup will keep the database smaller.

As I have insertedat, insertedby, updatedat, and updatedby fields in all my tables, this would also work, just one pair of these fields would be enough, if all data changes are captured, the initial date will be in the first cdc record.

Besides you can make use of sys.fn_cdc_map_time_to_lsn() to turn a time to a corresponding lsn to query for in the cdc data via the generated Table-valued functions.

It's all in the article I mentioned. REally nice, already forwarded it to someone else I know is doing audit tailing via application framework instead of triggers.

Bye, Olaf.