Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Creating a generic audit trigger with SQL 2005 CLR Expand / Collapse
Author
Message
Posted Tuesday, September 30, 2008 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2008 12:06 PM
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
Post #578648
Posted Monday, October 13, 2008 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 8:32 AM
Points: 22, Visits: 110
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!
Post #584983
Posted Wednesday, November 12, 2008 2:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 3:46 PM
Points: 20, 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.
Post #601715
Posted Wednesday, December 17, 2008 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 16, 2009 5:25 AM
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????
Post #621804
Posted Thursday, November 12, 2009 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 16, 2010 9:50 AM
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'
Post #818078
Posted Monday, February 1, 2010 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:39 PM
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
Post #857001
Posted Wednesday, November 3, 2010 2:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:49 PM
Points: 27, Visits: 193
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?

Post #1015065
Posted Wednesday, November 3, 2010 6:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1015643
Posted Thursday, May 19, 2011 10:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 3, 2011 11:39 PM
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
Post #1112158
Posted Sunday, October 21, 2012 10:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 35,372, Visits: 31,918
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375178
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse