Generic Audit Trigger
-
Hi All,
SQL Server 2000
Could someone please help me with the following…
I need to produce a generic trigger that can be applied to any table in the DB. It needs to work out what fields in the table have been changed then create a record in the Audit Header table then link to that one or more new rows in the Audit Detail table (one row for each field changed showing the original and new value for that field) .
Table structures as follows…
AuditHeader
AuditHeaderID int (auto increment)
AuditType varchar(10) (Insert / Update Delete)
ChangedBy varchar(10)
PCused varchar(50)
DateChanged datetime
Archived bit
AuditDetail
AuditDetailID int (auto increment)
AuditHeaderID (foreign key to parent header table)
FieldName varchar(50)
OldValue text
NewValue text
First I need to determine the change type I / U / D – I have this working. Then generate the header record and pick up its unique id – (how do I get this).
Next I need to be able to iterate through the different field names in the inserted and deleted tables – this is where I am stuck at the moment – and compare for changes. If they don’t match I need to get their name and datatype of the values (and convert the type if need be) then write them into the detail table.
Thanks for any ideas / suggestions.
CCB
-
Aren't you missing a "TableName" or TableID column?
--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) -
>>Then generate the header record and pick up its unique id – (how do I get this).
Right after you insert into the header table, do a SELECT @variable = SCOPE_IDENTITY() which will return the last value of the IDENTITY column that your session created a row for.
Also, the following function, when included in a trigger, will return the columns of a table that were updated. All you need to pass it is the table name and the COLUMNS_UPDATED() system variable. Use the function in a WHERE clause as if it were a table to create some dynamic SQL to do the rest.
CREATE FUNCTION dbo.fnColumnsUpdated
(@TableName SYSNAME, @BinaryColNums VARBINARY(128))
RETURNS @TableInfo TABLE (RowNum SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ColNum SMALLINT NULL, ColName VARCHAR(128) NULL)
AS
BEGIN --Begin the main body of the function
--===== Declare local variables
DECLARE @ByteCount TINYINT
SET @ByteCount = LEN(@BinaryColNums)
--===== Populate the return table with column numbers and names for the desired
--===== table according to the bit mask passed in through @BinaryColNums
INSERT INTO @TableInfo (ColNum,ColName)
SELECT c.ColNum, '['+COL_NAME(OBJECT_ID(@TableName), c.ColNum )+']' AS ColName
FROM (--Derived table "c" finds column numbers and names for columns identified
--in the @BinaryColNums variable usually provided by COLUMNS_UPDATED() in
--an INSERT or UPDATE table.
SELECT CASE ---- Determine which bit # is set and use a column number
WHEN BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1)
THEN ((ByteNum)*8)+t2.n --This creates the bit# that is set
ELSE 0
END AS ColNum
FROM (--Derived table splits the bytes of the binary number from left to
--right as outlined in Books OnLine for use of COLUMNS_UPDATED().
SELECT n-1 AS ByteNum,
SUBSTRING(@BinaryColNums,N,1) AS BinaryVal
FROM dbo.TALLY t1 WITH (NOLOCK)
WHERE N<= @ByteCount
) b,
dbo.Tally t2 WITH (NOLOCK)
WHERE N<=8 --8 bits in each byte
AND BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1) --Suppress "0" bits
) c
--===== If @BinaryColNums indicated more columns than there are, delete the ColNums from the table
DELETE @TableInfo
WHERE ColName IS NULL
--===== Return the table and exit
RETURN
END --End of Function
If you want to test the function outside of a trigger, do this...
SELECT * FROM dbo.fnColumnsUpdated('yourtablenamehere',0xffffffff)
... change the varbinary constant to see which column get's selected... keep in mind that it's "byte sliced" as in machine language level multi-byte listing.
You'll need a "Tally" table, as well... has dozens of uses... here's how to make one...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--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) -
Hi Jeff, Yes, I forgot to put on the TableName column in the original post.
Thanks very much for the rest of the info. I will give it a go.
CCB
-
Hi Jeff, I have just implemented it, but (forgive my ignorance), How do I actually link into it the old and new values of the changed items. The other thing is that this will fail if I update more than one row at once. What would be the best way to handle this ? (While loop for each record in the inserted / deleted table ?). My trigger as it stands looks as follows...
CREATE TRIGGER PickListItem_Audit ON dbo.tblPickListItem
FOR INSERT, UPDATE, DELETE
AS
Declare @Act Char(6)
Declare @del Bit
Declare @ins Bit
Declare @HeaderID Int
Declare @ColumnsUpdated varbinary
SET @del = 0
SET @ins = 0
If Exists (Select Top 1 1 From Deleted) Set @del=1
If Exists (Select Top 1 1 From Inserted) Set @ins = 1
If @ins = 1 AND @del = 1 Set @Act = 'Update'
If @ins = 1 AND @del = 0 Set @Act = 'Insert'
If @ins = 0 AND @del = 1 Set @Act = 'Delete'
If @Act <> 'Delete'
Begin
Insert into AuditHeader
(ChangeType,
TableName,
RecordID,
ChangeDateTime,
UserID,
UserPC,
Archived)
Select @ACT,
'tblPickListItem',
intID,
GetDate(),
User,
Host_Name(),
0
From Inserted
End
Else
Begin
Insert into AuditHeader
(ChangeType,
TableName,
RecordID,
ChangeDateTime,
UserID,
UserPC,
Archived)
Select @ACT,
'tblPickListItem',
intID,
GetDate(),
User,
Host_Name(),
0
From Deleted
End
Select @HeaderID = Scope_Identity()
Select @ColumnsUpdated = columns_Updated() from Inserted
Insert into AuditDetail
(AuditHeaderID,
FieldChanged)
Select @HeaderID,
A.ColName
From dbo.udf_auditColumnsUpdated('tblPickListItem', @ColumnsUpdated) A
Many Thanks
CCB
-
Your code to handle the header appears to have a couple of a problems... as you are aware, the Deleted table is empty for Deletes... why does the SELECT in the header code join to the Deleted table?
Also, you might want to dig into Books Online in the System Functions area... there you will find things like User_Name, Session_User, and Host_Name()... these could be used as defaults/formulas in your AuditHeader table to capture the information you require for UserID (ISNULL(Session_User,User_Name)) and UserPC (Host_Name()). No need to have that explicit code in the header code. Same goes for ChangeDateTime... use GETDATE() for the default on the column in the table.
The thing that is making the trigger fail for more than one row is the following...
Select @ColumnsUpdated = columns_Updated() from Inserted
If should simply be...
Select @ColumnsUpdated = columns_Updated()
... the way you had it, it would return the Columns_Updated() value more than once if there were more than one record in the Inserted table. It's a single value so as is Scope_Identity() so there is no need for the FROM clause.
For Deletes, you may need to set the @ColumnsUpdated variable to 0xFFFFFFFF because Columns_Updated() is 0 for deletes (if memory serves correctly).
You will, indeed, need a loop, but not row by row from the Inserted or Updated tables. Instead, loop through the column names provided by the udf_auditColumnsUpdated function (That's why the table output of the function has a RowNum in it). This will require some dynamic SQL to correctly insert records into AuditDetail based on the columns Deleted (you set @ColumnsUpdated to 0xFFFFFFFF), Inserted (system sets @ColumnsUpdated to all columns), and Updates (systems sets @ColumnsUpdated to just those columns updated).
Of course, that dynamic SQL will need to join to the Inserted/Deleted tables but the dynamic SQL is actually in a different scope and won't be able to see them. Soooo.... you will need to create a temp table for each. Since these triggers handle a relatively small number of records, it would probably be OK to use SELECT/INTO in this case to allow the code to be portable between tables.
To make life a bit easier and safer for copying the trigger in the future, I recommend you add one more variable at the very beginning of the trigger to store the table name. That way, you only need to change the code in one place for each table.
--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) -
Thanks Jeff,
I will give this a go.
CCB
-
...the Deleted table is empty for Deletes... Oh not it's not
Far away is close at hand in the images of elsewhere.
Anon. -
Dang... fat fingered that one
Meant to say that the deleted table is empty for INSERTs... thanks for the catch, David.--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) -
Be aware that Columns_Updated() does not indicate what is different only what was touched by the Sql statement.
-
Hi,
Thanks to your help Jeff, I am almost there. It audits changes made to multiple rows correctly, but when I update a single row and single field, it does not work for one of the fields (which happens to be the last field in the table). Single row for other fields and multiple values for a single / multiple rows work correctly. Any suggestions.
Thanks
CCB
CREATE TRIGGER PickListItem_Audit ON dbo.tblPickListItem
FOR INSERT, UPDATE, DELETE
AS
Declare @TableName Varchar(50)
Declare @KeyField Varchar(50)
Set @TableName = 'tblPickListItem'
Declare @Act Char(6)
Declare @del Bit
Declare @ins Bit
Declare @HeaderID Int
Declare @ColumnsUpdated Varbinary
SET @del = 0
SET @ins = 0
If Exists (Select Top 1 1 From Deleted) Set @del=1
If Exists (Select Top 1 1 From Inserted) Set @ins = 1
If @ins = 1 AND @del = 1 Set @Act = 'Update'
If @ins = 1 AND @del = 0 Set @Act = 'Insert'
If @ins = 0 AND @del = 1 Set @Act = 'Delete'
Declare @RecordID Int
Declare @RowNum Int
Declare @ColNum Int
Declare @ColName Varchar(50)
Declare @SQLString nVarchar(4000)
Declare @ParmDefinition nVarchar(4000)
Select * into #TempDeleted From Deleted
Select * into #TempInserted From Inserted
If @Act <> 'Delete'
Begin
Select @ColumnsUpdated = Columns_Updated()
Declare TempCursorHeader Cursor For
Select intID from Inserted
Open TempCursorHeader
Fetch Next From TempCursorHeader InTo @RecordID
While @@Fetch_Status = 0
Begin
Insert into AuditHeader
(ChangeType,
TableName,
RecordID)
Select @ACT,
@TableName,
intID
From Inserted
Where intID = @RecordID
Select @HeaderID = Scope_Identity()
Declare TempCursorDetail Cursor For
Select RowNum, ColNum, ColName from dbo.udf_auditColumnsUpdated(@TableName, @ColumnsUpdated)
Open TempCursorDetail
Fetch Next From TempCursorDetail InTo @RowNum, @ColNum, @ColName
While @@Fetch_Status = 0
Begin
Set @SQLString = N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue) Select @HeaderID, @ColName, Cast(B.' + @ColName + ' as VarChar(4000)) , Cast(A.' + @ColName + ' as Varchar(4000)) From #TempInserted A Left Outer Join #TempDeleted B on A.intID = B.intID Where A.intID = @RecordID'
Set @ParmDefinition = N'@HeaderID int, @ColName VarChar(50), @RecordID int'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @HeaderID = @HeaderID, @colName = @ColName, @RecordID = @RecordID
Fetch Next From TempCursorDetail InTo @RowNum, @ColNum, @ColName
End
Close TempCursorDetail
Deallocate TempCursorDetail
Fetch Next From TempCursorHeader InTo @RecordID
End
Close TempCursorHeader
Deallocate TempCursorHeader
End
Else
/* Begin
Declare TempCursorHeader Cursor For
Select intID from Deleted
Open TempCursorHeader
While @@Fetch_Status = 0
Begin
Insert into AuditHeader
(ChangeType,
TableName,
RecordID)
Select @ACT,
@TableName,
intID
From Deleted
Where intID = @RecordID
Fetch Next From TempCursorHeader InTo @RecordID
End
Close TempCursorHeader
Deallocate TempCursorHeader
End */
Begin
Set @ColumnsUpdated = 0xffffffff -- All columns --
Declare TempCursorHeader Cursor For
Select intID from Deleted
Open TempCursorHeader
Fetch Next From TempCursorHeader InTo @RecordID
While @@Fetch_Status = 0
Begin
Insert into AuditHeader
(ChangeType,
TableName,
RecordID)
Select @ACT,
@TableName,
intID
From Deleted
Where intID = @RecordID
Select @HeaderID = Scope_Identity()
Declare TempCursorDetail Cursor For
Select RowNum, ColNum, ColName from dbo.udf_auditColumnsUpdated(@TableName, @ColumnsUpdated)
Open TempCursorDetail
Fetch Next From TempCursorDetail InTo @RowNum, @ColNum, @ColName
While @@Fetch_Status = 0
Begin
Set @SQLString = N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue) Select @HeaderID, @ColName, Cast(A.' + @ColName + ' as VarChar(4000)) , null From #TempDeleted A Where A.intID = @RecordID'
Set @ParmDefinition = N'@HeaderID int, @ColName VarChar(50), @RecordID int'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @HeaderID = @HeaderID, @colName = @ColName, @RecordID = @RecordID
Fetch Next From TempCursorDetail InTo @RowNum, @ColNum, @ColName
End
Close TempCursorDetail
Deallocate TempCursorDetail
Fetch Next From TempCursorHeader InTo @RecordID
End
Close TempCursorHeader
Deallocate TempCursorHeader
End
-
William is correct... if you update a column to the same value, it will register as being updated even though it has the same value... it's up to your code to compare Inserted/Deleted values for changes... then, there's that NULL thingy...
--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) -
Oh jeez... If you're going to insult me with a cursor
Just kidding a bit... lemme see what I can do... I'll be back
--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) -
Ok... here it is... sans cursors... just so you know, I have NOT tested this particular code.
There are a couple of other things you need to know...
- You need to add a new column to the AuditHeader table called BATCH and it must be a VARBINARY(16). The addition of this column will allow us to do a certain amount of magic to make this almost 100% set based instead of using cursors and Scope_Identity(). Please see the comments in the code for additional information.
- I added a handful of variables and commented one out... @HeaderID is no longer required because of the bit of magic I did with the Batch column and the @Batch variable in the AuditDetail update(s).
- There is only one update for AuditDetail and it will handle Inserts, Updates, and Deletes.
- See the comments for how many columns this is limited to... right now, I have it set to a limit of 256 columns. If your tables have more than that, you may have bigger problems than you know
- I tried to keep your formatting, sort of... I also added a pot-wad of comments that you might want to consider leaving in the code so someone else can figure it out
- There is a way to make a temporary # proc that can take way more than a lousy 4k bytes of code... I just ran out of time on this so settled for an almost setbased loop with 1 interation for each column modified. It should still be nasty fast because it has very little RBAR in the code (RBAR... pronounced "ree-bar" and is a "Moden-ism" for "Row By Agonizing Row")
Here's the code... like I said, I've not tested this particular code but it is modeled after a similar thing I did at work...
CREATE TRIGGER PickListItem_Audit ON dbo.tblPickListItem
FOR INSERT, UPDATE, DELETE
AS
--===== Presets
Set Nocount On -- Prevents unexpected feedback to GUI's and increases speed a bit
--===== Declare local variables
Declare @TableName Varchar(50)
Set @TableName = 'tblPickListItem'
Declare @KeyField Varchar(50)
Declare @Act Char(6)
Declare @del Int
Declare @ins Int
--Declare @HeaderID Int
Declare @ColumnsUpdated Varbinary(32) --1 byte for every 8 columns... 32 = 256 columns
Declare @Batch Binary(16)
SET @Batch = NEWID()
Declare @RecordID Int
Declare @Counter Int
Declare @ColCount Int
Declare @RowNum Int
Declare @ColNum Int
Declare @ColName Varchar(50)
Declare @SQLString nVarchar(4000)
Declare @ParmDefinition nVarchar(4000)
--===== Create a table to hold the names of the columns affected by the query
Create Table #ColNames
(
RowNum Int Primary Key Clustered,
ColNum Int,
ColName SysName --SysName is really NVarChar(128)
)
--===== Determine what type of query fired the trigger and populate
-- temp tables for Inserted and Deleted records at the same time
Select * Into #TempDeleted From Deleted
Set @del = Sign(@@Rowcount)
Select * Into #TempInserted From Inserted
Set @ins = Sign(@@Rowcount)
Select @Act = Case
When @ins = 1 And @del = 0 Then 'Insert'
When @ins = 0 And @del = 1 Then 'Delete'
Else 'Update'
End
--===== Create the correct headers for all records at the same time
-- and identify which columns to audit based on query type.
-- Note that any given record will only be Inserted, Updated, or
-- Deleted once per query, no matter what, so each RecordID in
-- the trigger tables will be unique no matter what. This totally
-- eliminates the need for a cursor or a loop here... the Batch
-- column is the key that will allow us to match the header and
-- detail records.
If @Act = 'Delete'
Begin
--- Setup to handle up to 256 columns (one "FF" for every 8 columns)
Set @ColumnsUpdated = 0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
Insert into AuditHeader
(
ChangeType,
TableName,
RecordID,
Batch
)
Select
ChangeType = @ACT,
TableName = @TableName,
RecordID = intID,
Batch = @Batch
From Deleted
End
Else --===== @Act is 'Update' or 'Insert'
Begin
Set @ColumnsUpdated = Columns_Updated()
Insert into AuditHeader
(
ChangeType,
TableName,
RecordID,
Batch
)
Select
ChangeType = @ACT,
TableName = @TableName,
RecordID = intID,
Batch = @Batch
From Inserted
End
--===== At this point, we know enough to populate the col names table
-- and capture the number of columns that were affected by the query
Insert Into #ColNames
(RowNum,ColNum,ColName)
Select RowNum, ColNum, ColName
From dbo.udf_auditColumnsUpdated(@TableName, @ColumnsUpdated)
Set @ColCount = @@RowCount
--===== No matter what type of query fired the trigger, we're ready to rock
-- for updating the Audit detail. Because the dynamic SQL is so long,
-- we still need to loop through for each column updated to make sure
-- we don't run out of @SQLString variable space. This should be a bit
-- faster than a cursor.
Set @Counter = 1 --Assuming that SOMETHING fired the trigger at this point
Set @ParmDefinition = N'@Batch VarBinary(16)'
While @Counter <= @ColCount
Begin
SELECT @SQLString =
N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue)
Select h.AuditHeaderID,
FieldChanged = ' + cn.ColName + ',
OldValue = Left(Cast(d.'+ cn.ColName + ' as VarChar(8000)),4000),
NewValue = Left(Cast(i.'+ cn.ColName + ' as VarChar(8000)),4000),
From AuditHeader h
Left Outer Join #TempDeleted d On h.RecordID=d.intID
Left Outer Join #TempInserted i On h.RecordID=i.intID
Where h.Batch = @Batch'
From #ColNames cn
Where cn.RowNum = @Counter
Execute sp_ExecuteSQL @SQLString, @ParmDefinition, @Batch = @Batch
Set @Counter = @Counter + 1
End
--===== Do a little housekeeping in case the same session fires the same trigger
DROP TABLE #TempDeleted, #TempInserted, #ColNames
--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) -
Jeff, thanks for the update. You have been most helpful.
I only had to make the following changes (extra quotes and remove the red comma)
SELECT @SQLString =
N'Insert into AuditDetail (AuditHeaderID, FieldChanged, OldValue, NewValue)
Select h.AuditHeaderID,
FieldChanged = ''' + cn.ColName + ''',
OldValue = Left(Cast(d.'+ cn.ColName + ' as VarChar(8000)),4000),
NewValue = Left(Cast(i.'+ cn.ColName + ' as VarChar(8000)),4000),
From AuditHeader h
Left Outer Join #TempDeleted d On h.RecordID=d.intID
Left Outer Join #TempInserted i On h.RecordID=i.intID
Where h.Batch = @Batch'
From #ColNames cn
Where cn.RowNum = @Counter
Two problems however...
Deleting doesn't work - Get a "Error... Another user has modified the contents of this table or view. The database row you are modifiying no longer exists in the database." message.
I have the same problem as my previous version of the trigger - If I update a certain field in the table (which incidentally happens to be the last field) and no other fields at the same time, the second part of the trigger does not seem to fire (Inserting into the AuditDetail table). Any Ideas here. I tried writing into the header the columns changed value (casted to a varchar) in my version. When it worked I saw a Square character in the field. When it didn't work, there was no square written. If I update more than one column and include the problematic column in the update, it works fine. My original thought was it was the UDF that was the culprit, but I am not sure as I go a bit cross-eyed while reading through the UDF. Any suggestions most welcome.
Thanks
CCB
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply