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
Kkrs,
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|||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
>--Original Message--
>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:
> 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
>
>>--Original Message--
>>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
>>.|||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
>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:
>> 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
>>
>>--Original Message--
>>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,
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:
> 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
>>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:
>>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
>>
>>--Original Message--
>>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
>>.
>>
>>.|||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
>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:
>> 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
>>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:
>>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
>>
>>--Original Message--
>>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
>>.
>>
>>.
>.
>|||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:
> 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:
>>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