Creating history audit table data

Question

Post reply

Creating history audit table data

Ray K

SSC-Dedicated

Points: 31163
More actions
August 17, 2010 at 10:46 am

#236678

Hi all . . .
I'm working on creating a history/audit mechanism whenever tables are changed (INSERT/UPDATE/DELETE). I could use some input here.
Here's what I'd like to do. Let's say I have a table (let's call it #Names) that looks something like this:
create table #Names (
[NameID] int identity(1,1) not null,
[Name] nvarchar(50) not null,
[Address] nvarchar(50) not null
)
insert into #Names ([Name], [Address]) values ('Jeter, Derek', 'Yankee Stadium, Bronx, NY')
insert into #Names ([Name], [Address]) values ('Pujols, Albert', 'Busch Stadium, St. Louis, MO')
I also have a history table (#Names_Hist) that looks like this:
create table #Names_Hist (
[ChangeID] int not null, -- note that this is NOT an identity field, and it CAN have duplicate values -- will explain why in a minute
[ChangeType] nvarchar(10),
[NameID] int not null,
[Name] nvarchar(50) not null,
[Address] nvarchar(50) not null
)
Note that there can be several tables like these two.
When a table is modified, it kicks off a DML trigger that populates the history table -- maybe something that looks like this (using DELETE as an example):
insert into #Names_Hist select @ChangeID, 'DELETE', NameID, Name, Address from deleted
Hey, wait -- what's this about @ChangeID? Glad you asked. I would also like to include an update summary table (let's call it #UpdateLog) that looks something like this:
create table #UpdateLog (
[ChangeID] int identity(1,1) not null,
[ChangeDate] datetime not null,
[Action] nvarchar(10) not null,
[Description] nvarchar(256) not null
I would like to populate this table anytime a change is made to any of the other tables. I'd like to use the [ChangeID] generated from the INSERT INTO #UpdateLog statement to populate the [ChangeID] column in the other history tables.
I'm having some trouble trying to figure out how to do this. I've been reading about the OUTPUT clause in BOL, and am having trouble trying to apply it.
Any chance someone could point me in the right direction?
Thanks! (And my apologies for the lengthy post, but I hope it illustrates what I'm trying to do!)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

This help?

http://www.databasejournal.com/features/mssql/article.php/3598636/OUTPUT-clause-in-SQL-Server-2005.htm

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Steve Jones - Editor (8/17/2010)
This help?
http://www.databasejournal.com/features/mssql/article.php/3598636/OUTPUT-clause-in-SQL-Server-2005.htm

Somewhat. One thing that's confusing me: is OUTPUT only limited to storing in a table variable, or is there a way for me to store it in something else (for example, ChangeID into an integer variable)?

That's a big part of what I don't get, and I haven't found anything that explains that clearly.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

You don't want to use a variable. Think trigger. What if multiple rows are changed?

You use some table var and then drop that into your audit table.

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

Okay, I think I see what you're saying. Let me make sure I'm following this correctly.

Grab the rows from the inserted/deleted table and OUTPUT them into a table variable.
Get the ChangeID from inserting into the #UpdateLog table, and OUTPUT that into another table.
Use the table variable results to populate the audit/history table

Is that correct?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 5

Does it have to be a trigger?

Could you not change your code that populates the "other" tables into populating this table at the same time? Stored procedures and transactions work very well for this and don't carry the same processing burden as a trigger.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Ray K SSC-Dedicated Points: 31163 More actions · Answer 6

Brandie Tarvin (8/17/2010)
Does it have to be a trigger?

If you're asking if my code has to be in a trigger, then no, it doesn't. In fact, the original version of my code had my trigger calling a stored procedure, and having the majority of the processing handled by the SP.

I'm still thinking about that, but I'm just trying to figure out the best way to tackle it.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 7

If you can piggy-back your logging table stuff on the code that actually inserts the original data into the table, that would be the best of all worlds, IMHO. That way, you can do your multiple inserts without searching system temp tables for what you need, and you don't miss anything.

Wrap it all up in a Try-Catch / Transaction statement and viola', you have your faux-trigger without the trigger cost.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Ray K SSC-Dedicated Points: 31163 More actions · Answer 8

Brandie Tarvin (8/17/2010)
If you can piggy-back your logging table stuff on the code that actually inserts the original data into the table, that would be the best of all worlds, IMHO. That way, you can do your multiple inserts without searching system temp tables for what you need, and you don't miss anything.
Wrap it all up in a Try-Catch / Transaction statement and viola', you have your faux-trigger without the trigger cost.

Okay, I see what you're saying. The only trouble is that the code that performs the original data insertion is on the application side and outside the database (I did not make that decision; if it was completely up to me, everything would've been done with SPs). The piece I'm working on needs to capture all data changes, regardless of whether it was done through the database or through the application. Unfortunately, I don't see how that can be done without triggers.

If there's a way to do that without triggers, I'm all ears.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

Hi Ray

Not sure if the attached code is of any use to you, but may provide some ideas.

I run this on SS2K...mainly used to determine changes in an offline reporting dB that is replicated from production.

I really do not know what effect such a trigger would have on an OLTP production dB.

The principal busineess requirement I had was to only report the rows/columns that were changed per table for many tables and to show old and new values.

Anyway...hope this helps

Kind regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ray K SSC-Dedicated Points: 31163 More actions · Answer 10

Going to need some time to digest this code, but yes, I do think this will be helpful.

Thanks!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 11

Steve Jones - Editor (8/17/2010)
You don't want to use a variable. Think trigger. What if multiple rows are changed?
You use some table var and then drop that into your audit table.

BOL makes it sound like you have to use a table variable and patently, you do not. You can use a temp table or a real table.

For audit purposes, it's usually easier to do in a trigger if you know how to write multi-row triggers (which is also easy).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ray K SSC-Dedicated Points: 31163 More actions · Answer 12

I actually did end up putting something together that works.

Here's what I ended up doing.

I first did a simple count on the inserted and deleted tables in the trigger. This tells me whether the action is an update, insert, or delete. (I didn't want to write separate triggers for each action, because they all essentially run the same code.)
The trigger calls a stored procedure that updates the data in my #UpdateLog audit summary table. When it inserts the data, it returns the identity ID value (using the OUTPUT clause) for the new entry.
The ID is passed from the SP back to the trigger (using an OUTPUT parameter), and uses it, along with the original table, to populate my history/audit table.

I'll try to write up some dummy code to demonstrate what I'm doing. But the code does work. If I have time to post my code, I will do so; maybe you guys can tell me if I can make any tweaks to make it better.

Thanks, everyone, for your help (as always)!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 13

You say you are returning the ID value with an OUTPUT parameter. What do you return if you have multiple rows changed?

Ray K SSC-Dedicated Points: 31163 More actions · Answer 14

Steve Jones - Editor (8/18/2010)
You say you are returning the ID value with an OUTPUT parameter. What do you return if you have multiple rows changed?

I assume you mean the original table, Steve?

If multiple rows are changed (in the original table using the same action), they will have the same ID in the history table (in the history table, this is not an identity field, and it does allow duplicate values; this will act as a foreign key to the audit summary table as a one-to-many relationship). The audit summary table will have only one entry per table update (regardless of number of rows changed in the original table); in this table, the ID is an identity field and the primary key.

Does this sound viable, or do you have something that would be better?

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/