|
|
Posted by Rico on 01/16/07 14:27
Thanks Folks!
Erland, always helpful info. I don't know why I didn't think of the
Transaction Logs (oh, I know, it's my inexpreience!). I will definitely
take a look at that. One question, can the transaction logs be queried and
used to update a central repository? One of the requriements of this
application is to keep a log of any and all changes to the data at each
remote location. All remoted databases will update a central repository
"web" database that will be used to report on this info (which is part of a
study).
Right now, it looks like creating a duplicate of the original table would be
the most cost effective solution for an audit created in T-SQL, but if the
transaction logs are easily used and accessed then I suspect that would be
the way to go.
Rick
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns98B9F3D0E3384Yazorman@127.0.0.1...
> Rico (you@me.com) writes:
>> I'm creating an audit table and associated triggers to be able to
>> capture any updates and deletes from various tables in the database. I
>> know how to capture the records that have been updated or deleted, but
>> is there any way that I can cycle through a changed record, look at the
>> old vs new values and capture only the values that have changed?
>
> In a word: don't do it.
>
> Don't implment your own audit solution, when there are third-party
> solutions
> around. For instance have a look at
> http://www.apexsql.com/sql_tools_audit.asp-
>
> No, I have not used that tool, so I cannot vouch for whether it is good
> or not. But to be frank: if you have to ask how to write such an audit
> trigger, what are the odds that you would do it better?
>
> In order to "cycle through the columns" at run-time you would have to use
> dynamic SQL, and dynamic SQL comes with permissions problems. And it would
> be grossly ineffective. The correct way to go would be to write a program
> that generates a trigger that checks all columns individually. For tables
> with many columns this could still be less effecient that just saving the
> entire row to the audit table.
>
> Finally, I should say that an alternative to using triggers for auditing
> is to use the transaction log. There are several log readers out there. I
> see that ApexSQL has one. Red Gate has another. And, of course, Lumigent
> who implemented the first log reader are still in business. They also
> have a more versatile audit tool biuld on top of their log reader.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|