Solved

Catching table and column name for audit trigger?

Posted on 2002-05-28
10
1,657 Views
Last Modified: 2012-08-14
Hey all,
  I just had a request to create a means to audit updates in our SQL Server 7.0 Database.  Currently we have 20 tables and the client requested we create an audit table and trigger for each table to catch the old values after updates.  I thought it might be efficient to instead have one audit table having fields Id, TableName, ColumnName, OldValue, User, Date.  That way I could keep track of all updates in one table for any table in the database and to any column in the table.  Here is my question, is it possible to catch the Table and Column that is being updated in the update trigger in order to pass it through in an insert statement to the audit table?  That way I could have the exact same trigger on all 20 tables, and all the audit records would be held in one table.  Thanks for the help.  I would also appreciate any critiques on my idea.
0
Comment
Question by:jmouland
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 7040555
Hi,

Since each table needs its own trigger I don't see having the exact same trigger as an issue.

I don't know how to capture the table or column name.

But you could in each trigger declare a variable called TableName and set it to the appropriate value early on in the script so the triggers would be very similar ...

Regards
  David
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7040572
Something like this should give you some idea:

CREATE Trigger tr_Table1_Updated On dbo.Table1

After Update, Delete

As

If Update(Col1)
   Insert     Audit (
     TableName,
     ColumnName,  
     OldValue,
     NewValue,
     UserName)
   Select     'Table1',
     'Col1',
     Deleted.Col1,
     Inserted.Col1,
     USER
   From     Inserted
     Left Join Deleted On Inserted.Table1ID=Deleted.Table1ID
     
Notes (caveats):
1. You would have to repeat this If statement for each column.
2. Also, take a look at the Columns_Updated() function.  This is a bitwise operator that tells you which column(s) was(were) updated.  Unfortunately, if you want the original value, you will need to know the column name.
3. If a user is a member of sysadmin, the User function will return dbo.

Anthony
0
 
LVL 2

Expert Comment

by:pkohlmil
ID: 7040700
Some comments on your audit plan.
First, it seems like audits like this are done all the time but I can't name a third party product that just "does it" for you. If anybody knows of one, I would like to know.
Second, I hope you aren't using any of the BLOB datatypes.
Third, Columns_Updated()only works on the first 8 columns unless you trick it a bit. See the BOL entry for Create Trigger.
Forth, Here's another idea for finding the column that changed. I think Columns_updated() will be true for columns that were "changed" to their original value. It might be true that the application you are using simply updates all fields without checking to see if that is necessary. (For example, you create a COM object to do the update and you have an interface that takes 5 values and updates 5 columns even if only 4 are indeed different.) In that case, you could do your own check comparing the old (deleted) values and the new (inserted) values.
Fifth, the hard part in all of this is finding the column names, isn't it. You have to apply one trigger per table so you might as well have the table name in that trigger. You could hard code the column names as well but that gets rather brittle. You can write SQL to interrogate the column names which might be a good idea.

At one point I got tired of writing audit triggers for 70 tables and wrote myself a VB program to do it for me. I don't have that program with me (changed employers, didn't take all the code with me, good boy) but it wasn't all that hard to do. It allowed me to write the trigger once and have copies of it made and applied to all of the tables.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7041099
one more suggestion:
place the audit tables in a separate database or at least let those be owned by a different user and place them in a different filegroup... just for administrative tasks it will help you in the future.
CHeers
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041125
Hmmm.  This can get pretty hairy.  A trigger will request an exclusive lock on its table, so we can get the table name by selecting from syslocks to see on which user table the current spid is requesting an exclusive table lock:

Declare @Name NVarChar(150), @ID Int

Select Top 1 @Name = Name, @ID = ID
From Sysobjects SO, Master.dbo.SysLockInfo SL
WHERE SO.ID = SL.rsc_objid AND
    SO.Type = 'U' AND
    SL.req_spid = @@SPID AND
    SL.rsc_Type = 5 AND --Table
    SL.req_Mode = 8 -- Intended Exclusive lock

Now that we have the objid I recommend opening a cursor on the syscolumns table and for each column building a dynamic sql statement that does an insert into your audit table:


Declare @ColName VarChar(150), @SQL VarChar(1000)

Declare Columns Insensitive Cursor for
Select Name
From Syscolumns
Where ID = @ID
for Read_Only

Fetch Next from Columns Into @ColName

While @@Fetch_Status <> 0
Begin

    Set @SQL =
'Insert Into Audit Table
(TableName, ColumnName, RecID, OldData, NewData, UserID, UpdtDate)
 Select ' + @Name = + ', ' + @ColName + ', I.ID_Column, D.' + @ColName + ', I.' + @ColName + ', System_User, GetDate()
From Inserted I, Deleted D
Where I.ID_Column = D.ID_Column AND
      D.' + @ColName + ' <> I.' + ColName

    -- Print @SQL
    Exec(@SQL)
    Fetch Next from Columns Into ColName
End

I should point out that this will only work for an update trigger because the inserted and Deleted tables only both exist for an update.  Similar insert statements can be written for updates and deletes (It's actually alot simpler, you just need a select from inserted or deleted without the additional where conditions).  This way you only write three triggers (one for update, one for delete, and and one for insert) and apply the same three for each table you want to audit.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041131
I have my @@fetchStatus statement mixed up.  It should read:

While @@Fetch_Status = 0


Thanks =)
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7041137
. . . And there's a missing open command . . . here's the whole trigger again. Too much code I guess =). . .

Create Triger trAuditTrigger On Table For Update AS

Declare @Name NVarChar(150), @ID Int

Select Top 1 @Name = Name, @ID = ID
From Sysobjects SO, Master.dbo.SysLockInfo SL
WHERE SO.ID = SL.rsc_objid AND
   SO.Type = 'U' AND
   SL.req_spid = @@SPID AND
   SL.rsc_Type = 5 AND --Table
   SL.req_Mode = 8 -- Intended Exclusive lock

Declare @ColName VarChar(150), @SQL VarChar(1000)

Declare Columns Insensitive Cursor for
Select Name
From Syscolumns
Where ID = @ID
for Read_Only

Open Columns
Fetch Next from Columns Into @ColName

While @@Fetch_Status = 0
Begin

   Set @SQL =
'Insert Into Audit Table
(TableName, ColumnName, RecID, OldData, NewData, UserID, UpdtDate)
Select ' + @Name = + ', ' + @ColName + ', I.ID_Column, D.' + @ColName + ', I.' + @ColName + ', System_User,
GetDate()
From Inserted I, Deleted D
Where I.ID_Column = D.ID_Column AND
     D.' + @ColName + ' <> I.' + ColName

   -- Print @SQL
   Exec(@SQL)
   Fetch Next from Columns Into ColName
End
0
 

Author Comment

by:jmouland
ID: 7041555
Ok this will give you a locked table, but what if the user calls a procedure that does updates on multiple tables? Your TOP 1 might return the wrong tablename?  And also won't your column select give all columns for the table not just the ones that are updated?
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7041708
1.) Requesting an exclusive lock on a table is fairly rare.  In general the default behavour is the more fine grained row level or etent level locking

2.) Yes the column select will return all the columns but checking where Inserted.ColumnName <> Deleted.ColumnName in the insert statement for the audit table guarantees that the column has changed.  It's equivalent to 'If Update(ColumnName)' . . .
0
 

Author Comment

by:jmouland
ID: 7041835
Thanks so much, i'll give it a try:)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now