473,231 Members | 1,498 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Auditing:Extracting changed fields from Inserted table

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
Jan 15 '07 #1
6 5194
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
Jan 15 '07 #2
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.
Jan 15 '07 #3
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****@sommarskog.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
Jan 15 '07 #4
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.
Jan 15 '07 #5
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****@sommarskog.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****@sommarskog.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

Jan 16 '07 #6
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****@sommarskog.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
Jan 23 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Keith | last post by:
Not sure if anyone in here knows the answer to this, but I asked in a SQL group and haven't had a suitable answer and since the front end app is ASP I though I'd give here a try. I am trying to...
3
by: Bill Tepe | last post by:
I have a need to insert rows into an Audit type table when values change in certain fields in a table. I thought I could do this via a trigger. However, on requirement is to include in the audit...
10
by: Paulo Jan | last post by:
Hi all: Let's say I'm designing a database (Postgres 7.3) with a list of all email accounts in a certain server: CREATE TABLE emails ( clienteid INT4, direccion VARCHAR(512) PRIMARY KEY,...
5
by: trode | last post by:
Hello all. For the first time, our company might make use of the MS Access portion of the office package. Problem is, I don't know it well enough to whip out a desired program. What we want to...
1
by: Not Me | last post by:
Hey, In our database, we have implemented auditing simply by holding a copy of each row of the specific table at any time that data is updated. This gives us a long list of full records, often...
14
by: Jonas | last post by:
Hi! I'm developing the middletiers of an ASP.NET application in VB.NET. I've got a business logic layer in which I would like to perform auditing to a database. Instead of making an auditing...
3
by: DeanL | last post by:
Hi All, I need some help regarding the creation of an auditing facility with Access 97. First off, let me say that the following design is not my idea but I'm in the unfortunate position of...
1
by: binder | last post by:
I have a stored procedure that inserts values into five columns of a table. I need another stored procedure that will allow the user to pass one or more of those parameters and update only the...
0
by: JimLad | last post by:
Hi, I've been tasked with reviewing the Authentication and Auditing of an application and database. ASP/ASP.NET 1.1 app with SQL Server 2000 database. Separate audit trail database on same...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.