Keeping an audit trail

Hi,
Is there a best practice way of keeping an audit of data in sql server.
Specifically I need to allow users to undo changes to records, say the last
5 changes. I'm guessing I need to write this out to a secondary table.
Would appreciate any views on this.
Thanks
K
krs,
You can do this via trigges or stored procedures. Using stored
procedures, you will have to lock down your tables so that no-one can
access the data directly. If you want to cover all scenarios then you
need to go with triggers. Stored procs will perform better though, and
will be easier to manage.
You table design for audit can be one of two varieties:
1. You have a "copy" of all your tables in the database with some extra
metadata to describe the audited data like,
Action char(1) -- can be 'U' or 'D' for update, delete
DateAudited datetime DEFAULT GetDate()
UserName sysname default suser_sname()
2. The other method would be to construct a table where you just store
the changes to the data, not the entire rows that are changed. I favour
1 although it takes up more space should perform better and be easier to
manage, query.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krs wrote:
> Hi,
> Is there a best practice way of keeping an audit of data in sql server.
> Specifically I need to allow users to undo changes to records, say the last
> 5 changes. I'm guessing I need to write this out to a secondary table.
> Would appreciate any views on this.
> Thanks
> K
|||Peter,
I've implemented this many times and it is not such a big deal that
you're making out. Admittedly it will impact on the performance of the
database, but if implemented through triggers and you have one audit
table per table that you want to audit it should work nicely.
On a further note, you should only audit the tables you are interested
in and not do a blanket audit, however some clients I have worked for
have asked for a blanket audit on everything. In this case I created my
own audit generation system, which would auto-generate the triggers and
create the audit tables automatically. I might post it up on my site one
day, after I've tidied the code up. :-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter The Spate wrote:[vbcol=seagreen]
> Hi krs,
> This will be a nightmare, take it from me I had to
> implement one.
> How I finally implement this was that each change to the
> data did not result in an update, but an insert.
> I needed to define a couple of datetime fields per row
> which showed what the date was when the data was changed,
> i.e.
> The row was valid from (DateTime) to (DateTime) with both
> dates in as part of my primary key.
> So when something was changed I had to
> 1. Update the current rows date to.
> 2. Insert a new row with a new date from, leaving the to
> date blank.
> For ease sake I wrote this in the same table, rather than
> a secondary table, otherwise I would have twice as many
> tables as necessary.
> Now the fun does not stop here.
> When it comes to views, I had to provide a further filter
> checking there was no date to, so I could get the most up
> to date row.
> You select statement will need to have an optional
> parameter in it to see if there is a to date (i.e are you
> select the most current, or one in the past)
> The insert Store procedures all had to have both inserts
> and updates. (i.e updating the old row to have a To date,
> then inserting the new record)
> The update Store procedures all had to have both inserts
> and deletes (For the reason above, i.e. a persons surname
> has changed so you need to insert a new record, and keep
> the pre updated one)
> And the delete store procedures had to have code to delete
> all of the history.
> Finally you will need a mechnism to get rid of histories
> over one day old.
> All in all a complete nightmare.
> However it did allow the user to go through all the
> changes they made to the row, not that they actually used
> it in the end.
> If you can convince your boss its a bad thing then please,
> please do so.
> N.B. Marks sugestion normally would work, but its the bit
> about the users undoing changes themselves is the problem.
> If you need any further help on this then my email is
> peternolan67REMOVETHIS@.hotmail.com.
> Good luck
> Peter
> "Facts are stupid things."
> Ronald Reagan
>
> in sql server.
>
> records, say the last
>
> secondary table.
|||Thanks Mark,
The main problem here was not that they want and audit
trail as the title sugests but they want to see what
changes have been made to the system.
On the system I worked on (so I probably a bit blinded by
it), not only did they want to see the history, but the
ability to roll it back afterwards, and thats where it
turned out to be a complete dba nightmare.
Anyway if I wrong I'm wrong, so I will defer to your
greater experience.
Peter

>--Original Message--
>Peter,
>I've implemented this many times and it is not such a big
deal that
>you're making out. Admittedly it will impact on the
performance of the
>database, but if implemented through triggers and you
have one audit
>table per table that you want to audit it should work
nicely.
>On a further note, you should only audit the tables you
are interested
>in and not do a blanket audit, however some clients I
have worked for
>have asked for a blanket audit on everything. In this
case I created my
>own audit generation system, which would auto-generate
the triggers and
>create the audit tables automatically. I might post it up
on my site one[vbcol=seagreen]
>day, after I've tidied the code up. :-)
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Peter The Spate wrote:
the[vbcol=seagreen]
changed,[vbcol=seagreen]
both[vbcol=seagreen]
to[vbcol=seagreen]
than[vbcol=seagreen]
filter[vbcol=seagreen]
up[vbcol=seagreen]
you[vbcol=seagreen]
inserts[vbcol=seagreen]
date,[vbcol=seagreen]
inserts[vbcol=seagreen]
surname[vbcol=seagreen]
keep[vbcol=seagreen]
delete[vbcol=seagreen]
histories[vbcol=seagreen]
used[vbcol=seagreen]
please,[vbcol=seagreen]
bit[vbcol=seagreen]
problem.[vbcol=seagreen]
data
>.
>
|||krs,
In addition to mine and Peter's comments, you may just wish to view the
transaction log and manipulate it using Lumigent Log Explorer.
This allows you to undo transactions, and may be the best way forward
for you. They also have an audit tool called Entegra.
http://www.lumigent.com
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krs wrote:
> Hi,
> Is there a best practice way of keeping an audit of data in sql server.
> Specifically I need to allow users to undo changes to records, say the last
> 5 changes. I'm guessing I need to write this out to a secondary table.
> Would appreciate any views on this.
> Thanks
> K
|||Peter, the undoing of transactions is the tricky bit, you're right. The
setting up of the audit and viewing it is easy. (I just re-read the
post). :-/
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Peter The Spate wrote:[vbcol=seagreen]
> Thanks Mark,
> The main problem here was not that they want and audit
> trail as the title sugests but they want to see what
> changes have been made to the system.
> On the system I worked on (so I probably a bit blinded by
> it), not only did they want to see the history, but the
> ability to roll it back afterwards, and thats where it
> turned out to be a complete dba nightmare.
> Anyway if I wrong I'm wrong, so I will defer to your
> greater experience.
> Peter
>
> deal that
>
> performance of the
>
> have one audit
>
> nicely.
>
> are interested
>
> have worked for
>
> case I created my
>
> the triggers and
>
> on my site one
>
> the
>
> changed,
>
> both
>
> to
>
> than
>
> filter
>
> up
>
> you
>
> inserts
>
> date,
>
> inserts
>
> surname
>
> keep
>
> delete
>
> histories
>
> used
>
> please,
>
> bit
>
> problem.
>
> data
|||It took 6 months out of my life...
I still get the flashbacks :-D
Peter

>--Original Message--
>Peter, the undoing of transactions is the tricky bit,
you're right. The
>setting up of the audit and viewing it is easy. (I just
re-read the[vbcol=seagreen]
>post). :-/
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>Peter The Spate wrote:
by[vbcol=seagreen]
big[vbcol=seagreen]
up[vbcol=seagreen]
many
>.
>
|||Thanks for your replys.
Really my aim is to be able to roll back certain data within the same
database. for example, in one table I hold data for many different business
areas. If data is lost in one area and I need to rollback, then all area's
would be rolled back using a restore from backup.
Is there a best practice solution to this sort of scenario?
Thanks
K
"Mark Allison" wrote:

> krs,
> In addition to mine and Peter's comments, you may just wish to view the
> transaction log and manipulate it using Lumigent Log Explorer.
> This allows you to undo transactions, and may be the best way forward
> for you. They also have an audit tool called Entegra.
> http://www.lumigent.com
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> krs wrote:
>
|||Would point-in time transaction log backups work for you - dump them
every 5 mins? If not, Log explorer would do the trick.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krs wrote:[vbcol=seagreen]
> Thanks for your replys.
> Really my aim is to be able to roll back certain data within the same
> database. for example, in one table I hold data for many different business
> areas. If data is lost in one area and I need to rollback, then all area's
> would be rolled back using a restore from backup.
> Is there a best practice solution to this sort of scenario?
> Thanks
> K
> "Mark Allison" wrote:
>