Creating a generic audit trigger with SQL 2005 CLR

  • To everyone complaining about the specifics of the AUDIT table in this article: you are missing the point. It is not my intention to suggest that this is the ideal format for an audit table to be used in every application. I am not recommending that you use single-field autoincrement keys, nor do I prohibit you from adding code to capture all the "insert" data if you want to be able to reconstruct complete records, nor do I recommend this sort of row-per-column-change table for high traffic situations. With a few hours of effort you can obviously convert this code to support almost any kind of audit table that you want. I had to choose SOME sort of audit table for the purpose of illustration; obviously any choice could have been subject to the same sorts of criticisms.

    The purpose of the article is to demonstrate a capability of the CLR. Since triggers require no parameters, you can dispense with the tedious business of writing a code generator to generate a custom SQL trigger for every single table (or, heaven forbid, writing such code by hand). Instead of miles and miles of SQL, you can now implement a trigger for each table in your app with just 3 lines of mindless SQL code.

    Yes I realize you still must write code generators (or, heaven forbid, write code by hand) to create SQL for all other CRUD operations. That's unfortunate, but Rome wasn't built in a day. I am hoping that this article will demonstrate to people at Microsoft that the CLR, if enhanced, can perhaps be used to eventually replace all of the enormous CRUD code that most of us have to generate.

    Thanks for reading and contributing!  - the author

  • Well, perhaps my problem is that I converted the code to C# and modified it to meet our specific needs.  But I slashed it to the simplest code and just tried to execute the highlighted statement and still got the message.  Is C# more rigorous in it's testing of required paramenters than VB.Net?  (btw. the documentation says the "target" is required.)

    Perhaps I'll give it a try in VB to see if that's the difference.  If so, I'd be nervous about Microsoft's intent and the stability of the aproach.

     

  • Stan Hargrove: Rather than convert back to VB, you could also try just choosing some arbitrary table name (real or perhaps even nonexistent) and use that. The CLR might not require the table to exist at compile time, and even if it did, you could just choose a real table name in each different app. I would also guess that the Target attribute is used purely for deployment purposes, i.e. it shouldn't change the behavior of this code. And as I point out in the article, even when the compiler accepts the VB code with the missing Target attribute, the deployment features obviously won't work for a table-agnostic solution like this one anyway.

    With regards to the stability of the solution. In 2005 I attended PDC and had a chance to talk to some of Microsoft's database people, who thought this concept was "cool". I pointed out both of the problems mentioned in my two "tech notes" (the other problem being the failure of the CLR to populate the TableName on the Inserted and Deleted tables). They didn't make any promises, but I take their interest as a hopeful sign that they will change the CLR to enhance the CLR's table-agnostic capabilities rather than eliminate them.

  • Here is a simple script that will create the audit trigger for all user tables in your database. Obviously, if you want to not do it for all tables, modify the script accordingly... Thanks David for the example. In my case, the project Im using this on needs *every* change logged even if I don't know who the user is so this is useful to me.

    -- Associate the generic CLR trigger with all user tables that aren't the audit

    -- table and are not the mysterious dtProperties table which is labeled as a user

    -- table as well...

    declare @table varchar(128)

    declare curTables cursor for

    select name from sysobjects where xtype = 'U' and name != 'Audit' and category != 2

    open curTables

    fetch next from curTables into @table

    while @@fetch_status = 0

    begin

    declare @sql varchar(1024)

    declare @triggername varchar(128)

    set @triggername = 'Audit_' + @table

    if exists(select name from sysobjects where name = @triggername and xtype = 'TA')

    begin

    --drop the trigger

    set @sql = 'drop trigger ' + @triggername

    print @sql

    execute(@sql)

    end

    set @sql = 'create trigger ' + @triggername + 'on ' + @table + ' for insert, update, delete as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon'

    print @sql

    execute(@sql)

    fetch next from curTables into @table

    end

    close curTables

    deallocate curTables

  • TECH NOTE 2: The CLR trigger routine relies on a field naming convention to extract the target table name because the "TableName" fields (i.e. DataTable.TableName in the code) of the "inserted" and "deleted" trigger tables are not being properly populated by SQL Server with the name of the target table (ironically these pseudo-tables contain essentially all the information you could want to know about the target table except its name). The convention involves using the table name as part of the name of the first (key) field. Obviously if your database does not employ such a convention, this trigger routine will be unable to provide the table name in your audit table. We have been informed by Microsoft that in a future release, the table name may be provided somewhere within the trigger context, but this capability will not be present in the initial release of SQL Server 2005

    Does anyone know if this has been fixed in SQL 2005 or do we have to wait until SQL 2008?

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • 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-2 int, @sql varchar(4000), @ColumnName varchar(200)

    set @x=1

    set @sql=''

    SELECT @max-2=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-2)

    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

  • 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:

  • 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.

  • 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????

  • 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-2=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'

  • 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

  • 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?

  • 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.

  • 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

  • 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.
    "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)
    Intro to Tally Tables and Functions

Viewing 15 posts - 16 through 30 (of 34 total)

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