Auditing:Extrac ting changed fields from Inserted table

[Rico]

Hello,

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?

To give you a better idea of what I'm trying to do, instead of creating a
copy of the original table (some tables have many fields) and creating a
whole record if a type or bit field has been changed, I'd like to only
capture the change in a single audit table that will have the following
fields;

AuditID int INDENTITY(1,1)
TableName varchar(100)
FieldName varchar(100)
OldValue varchar(255)
NewValue varchar(255)
AuditDate datetime DEFAULT(GetDate ())

Any direction would be greatly appreciated.

Thanks!
Rick

[dmarkle]

Rico:

There are 2 pseudo-tables inside an update trigger, called "inserted"
and "deleted". You can deduce through the values in these tables what
has changed. Updates look like a "delete" and an "insert" in your
triggers.

I'm not so sure about this design, though. In the long run, you may
find it to be quite limiting. I usually recommend against doing
auditing on the database level whenever I can -- I prefer to audit the
actions that users do in the application layer. Not to mention the
fact that I think your design will be slow and difficult to query when
the time comes to use it.

-Dave
Rico wrote:

Hello,

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?

To give you a better idea of what I'm trying to do, instead of creating a
copy of the original table (some tables have many fields) and creating a
whole record if a type or bit field has been changed, I'd like to only
capture the change in a single audit table that will have the following
fields;

AuditID int INDENTITY(1,1)
TableName varchar(100)
FieldName varchar(100)
OldValue varchar(255)
NewValue varchar(255)
AuditDate datetime DEFAULT(GetDate ())

Any direction would be greatly appreciated.

Thanks!
Rick

[Ed Murphy]

dmarkle wrote:

I'm not so sure about this design, though. In the long run, you may
find it to be quite limiting. I usually recommend against doing
auditing on the database level whenever I can -- I prefer to audit the
actions that users do in the application layer.

Auditing on the database level may be necessary if you can't alter the
application, or don't know which part is responsible.

Not to mention the
fact that I think your design will be slow and difficult to query when
the time comes to use it.

I don't see anything particularly wrong with the audit table, assuming
proper indexes.

[Erland Sommarskog]

Rico (yo*@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[dmarkle]

I apologize if I sound celkoesque (you can say you were there when I
coined this term) but...

I've seen a lot of tables like this. They suffer from a couple of
problems:

1) They're not typed. Who's to say that everything in your database
will be smaller than VARCHAR(255)? What happens if someone changes the
date format of the machine?
2) They usually indicate that the requirements of the application being
designed haven't been really scrutinized. What auditors generally want
to see is something along the lines of: "Tammy Jones put a SELL order
on AAPL at 86.49", not a bunch of individual column changes.
2.1) They don't tie together bits of a more complex transaction into
one cohesive whole. It's hard to see what LOGICALLY happened to make
the data change in the database. That's a lot more important when
you're doing things like trying to make sure people aren't doing what
they aren't supposed to do (which is the whole purpose of auditing,
right?)
3) They tend to become massive bit-buckets of write-only data, which
may just be the bane of my existence. Before going to this level, the
application designer really needs to look at the system requirements.
Who's going to be reading this data? How are you going to reconstruct
it for the reader in a meaningful way?

That being said, you *can* do this -- it will (most of the time)
"work". I've been forced to make tables like this every now and then,
and the result is just about always the same. What should be a 100MB
database becomes 10+GB of stale data that the auditiors frankly don't
care about at all. I'm not saying that you should never implement
designs like this. I'm just saying that a design like this must be
done only under *very* careful consideration -- not because the
designer didn't want to study the system's requirements and felt that
they'd be 'safe' by just 'auditing everything'.

-Dave



Ed Murphy wrote:

dmarkle wrote:

I'm not so sure about this design, though. In the long run, you may
find it to be quite limiting. I usually recommend against doing
auditing on the database level whenever I can -- I prefer to audit the
actions that users do in the application layer.

Auditing on the database level may be necessary if you can't alter the
application, or don't know which part is responsible.

Not to mention the
fact that I think your design will be slow and difficult to query when
the time comes to use it.

I don't see anything particularly wrong with the audit table, assuming
proper indexes.

[Rico]

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" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...

Rico (yo*@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

My news server had a crash, so I have not been able to access this group
for a week. But I saw on Google that Rico had some questions to my post:

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?

No, transaction logs as such cannot be easily queried. The format is
proprietary. But there are third-party log-reader tools that are able
to display the information in the log, and also more versatile tools
that can present the data for audit purposes. At least Lumigent has
such a tool, and you should definitely check this out.

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).

That's a very tall order, and I would suspect that the third-party
tools that I mention do not fit in here. But it also seems that
someone decided for a solution without considering alternatives first.

The problematic things here is that updating remote tables in the
triggers makes me very nervous about performance. If there is frequent
action on these tables, the system may grind to a standstill.

Alternatives? One way is to have the audit tables locally, and then
have some other process that moves the data to the central repository.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx