SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a generic audit trigger with SQL 2005 CLR


Creating a generic audit trigger with SQL 2005 CLR

Author
Message
jstuparitz
jstuparitz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
Just to provide a complete solution for everyone for George's suggestion see below. We've used this successfully and performance is excellent. For tables with binary/text columns you will need to change trigger a bit.
Hope this helps. This will only work with Sql Server 2005 and above.


CREATE TABLE [dbo].[Audit](
[AuditId] [bigint] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserId] [int] NULL,
[SystemUser] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Operation] AS (case when [OldValue] IS NULL then 'Insert' else case when [NewValue] IS NULL then 'Delete' else 'Update' end end),
[OldValue] [xml] NULL,
[NewValue] [xml] NULL,
[OldBinaryValue] [varbinary](max) NULL,
[NewBinaryValue] [varbinary](max) NULL
)


CREATE TABLE [dbo].[AuditTable](
[TableName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IdentityColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserIdColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED
(
[TableName] ASC
)


create proc [dbo].[SaveAudit]
@OldValues xml,
@NewValues xml

as

Declare @TableName varchar(128), @SystemUser varchar(80), @UserId int,
@ColumnName varchar(128), @Operation varchar(10), @sql varchar(1000)

SELECT @SystemUser=system_user

SELECT @TableName=object_name(resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE request_session_id = @@spid and resource_type = 'OBJECT'

Insert Into Audit (TableName, UserId, SystemUser, OldValue, NewValue)
Values (@TableName, null, @SystemUser, @OldValues, @NewValues)
go


CREATE proc [dbo].[GetAuditInfo]
@TableName varchar(128),
@StartDate datetime,
@EndDate datetime
as

set nocount on

Declare @x int, @max int, @sql varchar(4000), @ColumnName varchar(200)
set @x=1
set @sql=''

SELECT @max=count(c.[name])
FROM sys.all_columns c
INNER JOIN sys.all_objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.[name] = @TableName AND o.[type] = 'U'

while (@x <= @max)
Begin

SELECT @ColumnName=c.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.[name] = @TableName and c.column_id=@x AND o.[type] = 'U'

set @sql=@sql+'v.value(''@'+@ColumnName+''', ''varchar(8000)'') as '+@ColumnName
if @x<@max
set @sql=@sql+', '

set @x=@x+1
End

Declare @sql2 varchar(8000)

set @sql2='SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+
' Into ##340834audit FROM Audit CROSS APPLY OldValue.nodes(''/deleted'') x(v)
Where TableName='''+@TableName+''''

exec(@sql2)
set @sql2=''
set @sql2='Insert Into ##340834audit SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+
' FROM Audit CROSS APPLY NewValue.nodes(''/inserted'') x(v)
Where TableName='''+@TableName+''''

exec(@sql2)

select * from ##340834audit Order By TableName, DateCreated, SystemUser, Operation
drop table ##340834audit
go


And finally.....a generic audit trigger for your tables

Create trigger [dbo].[AuditYourTable] on [dbo].[YourTable]
for insert, update, delete
as
declare @i xml, @d xml
set @i = (select * from inserted for xml auto)
set @d = (select * from deleted for xml auto)
exec SaveAudit @d, @i
GO
chris-650182
chris-650182
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 111
jstuparitz (9/30/2008)
Just to provide a complete solution for everyone for George's suggestion see below. We've used this successfully and performance is excellent. For tables with binary/text columns you will need to change trigger a bit.
Hope this helps. This will only work with Sql Server 2005 and above.


This is really great. Solves the problem of too much overhead with the OO method, but still allows for a complete ability to see changes to rows.

Thanks! w00t
Jason Liveston
Jason Liveston
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 138
The issue I don't see anyone address is when you need to see what values were actually changed. The updated(), columns_updated() and sys.fn_IsBitSetInBitmask can all be used to get the fields changed but in each case you get all fields that are in the UPDATE statement.

So if you are just updating the same value (ex. firstname = Jason, updated statement updates same value from a textbox with Jason, value hasn't actually changed from application view) these functions are useless! The only way to solve this is to write complex triggers to compare the inserted and deleted values and then get the column that were changed, or you could deal with this from the application site but you will have to do a lot of processing and your stored procedures will have to be smart to only update particular fields that actually changed.
satyawan_sanap
satyawan_sanap
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
set @sql = 'create trigger ' + @triggername + ' on ' + @table + ' for insert, update, delete as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon'

what above statement does???? what AuditCommon is????
esteban-751363
esteban-751363
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 29
jstuparitz (9/30/2008)
Just to provide a complete solution for everyone for George's suggestion see below. We've used this successfully and performance is excellent. For tables with binary/text columns you will need to change trigger a bit.
Hope this helps. This will only work with Sql Server 2005 and above.


Thank you, thank you, thank you!

BTW what is [dbo].[AuditTable] for? Is not used anywhere.

For last in my case I got an error where the following SQL returned more than once the same column so I had to add the DISTINCT


SELECT @max=COUNT(DISTINCT c.[name])
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE o.[name] = @TableName
AND o.[type] = 'U'

plykkegaard
plykkegaard
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
Swirl80 (5/12/2008)
Does anyone know if this has been fixed in SQL 2005 or do we have to wait until SQL 2008?

Check this article
http://msmvps.com/blogs/theproblemsolver/archive/2007/02/19/determining-the-table-updated-inside-of-a-sqltrigger.aspx

hth /Peter
geerobg
geerobg
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 194
Sorry for the late bump.

We implemented a solution to address audit concerns by:

1. Creating an internal tool to define:
A. Schema of tables of audit interest and their related look up tables (to capture lookup values at trigger execution... As raw foreign key values can have their relevant values changed (lookup table's state Id = 1 state name could be changed from 'Arizona' to 'Texas'))
B. Provide 'Friendly' field names and relationships.

2. Create an end user tool which creates high performance, hard coded field name triggers which capture end user specified audit information.

3. End user tool to view and mark 'reviewed' status of information.

This strategy places the ball in the client's court to define relevant audit information. Generally, an intermediate developer can be tasked with updating the schema and then the client becomes wholly responsible for audit.


Any thoughts?
David Ziffer
David Ziffer
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 203
geerobg (11/3/2010)
Sorry for the late bump ...

This strategy places the ball in the client's court to define relevant audit information. Generally, an intermediate developer can be tasked with updating the schema and then the client becomes wholly responsible for audit.


Any thoughts?


Actually in this day of multi-terabyte disks I have given up entirely on incremental and/or partial audits entirely. I now have an approach that audits absolutely everything. It is illustrated in an article series that started a few weeks ago and is still running now. It's called "Building Nearly Codeless Apps" and you can find all the parts of it by searching on that string. Auditing is just one aspect of it.
Warwick Wood
Warwick Wood
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 32
I've been reviewing a lot of these options, and it seems that generic triggers are mostly horrendous to read/understand/maintain and/or have performance problems. I've nearly finished developing a generic stored proceedure that can add a table specific trigger to each table, for all columns. This has the advantage that the code that generates the trigger is generic, but the trigger itself is specific, and hence is high performance. It can also be re-run periodically if the table schema changes. I will post it here when done.

However, I don't want to log updates (or deletes, but thats another topic) where they are caused by cascading from a parent table, and am having trouble identifying these. It's not a "recursive_trigger" issue as the update (or delete) occurs as a result of a cascade (recursive triggers are off anyway).

Any advise on how to exit a trigger if it is the result of a cascade would be much appreciated.

Warwick
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211159 Visits: 41977
Warwick Wood (5/19/2011)
I will post it here when done.

Just a followup. Were you ever able to complete this?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search