Click here to monitor SSC
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20919 Visits: 9671
How do you scan this table to recreate the history of a row(s)? Or to rollback a group of transaction?
Amit Lohia
Amit Lohia
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 174

We can argue on any solution for Audit. Whether to create a history table or create a generic table with information about only the data changed and do not worry about the deletes (or design the table in a way whether their is no physical delete but just logical ones).

What I have learned from experience we cannot have BEST Pratice design or coding style as it changes drastically from project to project. Just go with the follow.

I will still thank the Author to have his idea on the table.

 

 

 




Kindest Regards,

Amit Lohia
George Palacean
George Palacean
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 185
It's possible to see how the data in the table looked at a previous moment.

The table being defined as:
create table MyTable (
a int not null primary key,
b int,
c int,
description varchar (100)
)

The function MyTable_History will return the data from the table as existed at the @Date moment.

create function MyTable_History (@Date datetime)
returns @r table (a int primary key, b int, c int, Description varchar (100))
as
begin

   insert into @r (a, b, c, Description)   --make a copy of the table.
   select a, b, c, Description
   from MyTable

   declare @inserted xml, @deleted xml

   --We will apply all the changes made to the table in the reversed order
   declare c cursor local for
   select OldValues, NewValues
   from DMLLogs
   where DateCreated >= @Date
   order by DMLLogID desc
   
   open c
   fetch next from c into @Deleted, @Inserted
   
   while @@fetch_status = 0 begin
   
      if @Inserted is not null and @Deleted is not null --update operation
         update @r set
            b = T.c.value ('@b', 'int'),
            c = T.c.value ('@c', 'int'),
            Description = T.c.value ('@Description', 'varchar (100)')
         from @R R inner join @Deleted.nodes ('deleted') T(C)
            on R.a = T.c.value ('@a', 'int')
            
      if @Deleted   is null --insert operation; must be deleted
         delete from @R
         from @R R inner join @Inserted.nodes ('inserted') T(C)
            on R.a = T.c.value ('@a', 'int')
            
      if @Inserted is null --delete operation; must be inserted back into the table
         insert into @R (a, b, c, Description)
         select T.c.value ('@a', 'int'),
            T.c.value ('@b', 'int'),
            T.c.value ('@c', 'int'),
            T.c.value ('@Description', 'Varchar (100)')
         from @Deleted.nodes ('deleted') T(C)
         
      fetch next from c into @Deleted, @Inserted
   end
   
   close c
   deallocate c
   
   return
end
go

The shortcoming is that this function does not handle correctly cases where the primary key (column a) was changed as a result of a update operation.
Also it will be very slow on large tables. For those it will be more efficiently to directly update the table, as the following procedure does:

create procedure Recover_MyTable
   @Date datetime
as
begin tran

   declare @inserted xml, @deleted xml
   declare c cursor local for
   select OldValues, NewValues
   from DMLLogs
   where DateCreated >= @Date
   order by DMLLogID desc
   
   open c
   fetch next from c into @Deleted, @Inserted
   while @@fetch_status = 0 begin
   
      if @Inserted is not null and @Deleted is not null --update operation
         update MyTable set
            b = T.c.value ('@b', 'int'),
            c = T.c.value ('@c', 'int'),
            Description = T.c.value ('@Description', 'varchar (100)')
         from MyTable R inner join @Deleted.nodes ('deleted') T(C)
            on R.a = T.c.value ('@a', 'int')
            
      if @Deleted   is null --insert operation; must be deleted
         delete from MyTable
         from MyTable R inner join @Inserted.nodes ('inserted') T(C)
            on R.a = T.c.value ('@a', 'int')
            
      if @Inserted is null --delete operation; must be inserted back into the table
         insert into MyTable (a, b, c, Description)
         select T.c.value ('@a', 'int'),
            T.c.value ('@b', 'int'),
            T.c.value ('@c', 'int'),
            T.c.value ('@Description', 'Varchar (100)')
         from @Deleted.nodes ('deleted') T(C)
         
      fetch next from c into @Deleted, @Inserted
   end
   
   
   close c
   deallocate c

commit tran   

go

You can test the procedure using the following code:

begin tran
go
--load some data into the table
insert into MyTable (a, b, c, description)
select 1, 2, 3, 'first inserted'
union all
select 2, 3, 4, 'inserted'

waitfor delay '0:00:01' --wait a second because SQL has only 3 ms time resolution and we risk to have the same timestamp

declare @d datetime set @d = getdate() --store the time
waitfor delay '0:00:01'

-- change the data
insert into MyTable (a, b, c, description)
select 100, 2, 3, 'inserted'
union all
select 200, 2, 3, 'inserted'

update mytable set b = 5
delete from mytable where a = 2

select * from MyTable --watch the current data
exec Recover_MyTable @d --recover the old data
select * from MyTable --the changes are undone
go
rollback



That's it.


PS. Does anyone know how to insert tabs in the message ?
Stan Hargrove
Stan Hargrove
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: 7

When I read this article, it seemed like an absolutely correct concept for METADAT tables.  I think it grossly inefficient for large DATA tables, hoewever.) Except:

1.  It never showed either the original inserted or complete deleted record.  Fields are shown only for updates.  Adding the fields for deletes shouldn't put too much burden on the system on tables that have a small portion of deletes. 

2.  Assembling views of a record at any point in time would take some (clr) programming, but the logic could be built once and used for all tables.

3.  Similarly, the difficult logic of using an audit trail to rollback a table to some pervious state could be programed just once for all audited tables.

4.  It simplifies the task of identifying all the changes made by an individual during a time frame regardless of the table involved.  I think this single source is a good auditing tool.

My biggest problem, however, is that I can't make it work.  WHen I try to compile just the shell of the code, I find the compiler will not allow me to skip the Target attribute in

    <Microsoft.SqlServer.Server.SqlTrigger(Name:="AuditCommon", Event:="FOR UPDATE, INSERT, DELETE")> _

  I wonder if this article, which was originally written before SQL2005 was released, is now out of date and MS no longer allows it.  It seems the author anticipated this when he wrote:

''' Note that the "SqlTrigger" attrbute does not contain one of its normal tags; namely it does not
    ''' specify any particular table. We don't know if it is Microsoft's intention to allow table-agnostic
    '''trigger code, but this works and we hope that it keeps working.

 


David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 199

Stan Hargrove: Thanks for trying out my solution. I don't understand why you had a problem with compilation of the missing Target attribute though. I compiled the code in the article in July 2006 just before publishing it in SqlServerCentral, and it worked just fine. My version is:

Microsoft Visual Studio 2005
Version 8.0.50727.42  (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005   77626-009-0000007-41371
Microsoft Visual Basic 2005

- The author


David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 199

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


Stan Hargrove
Stan Hargrove
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: 7

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.

 


David Ziffer
David Ziffer
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 199

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.


Jess Askey-260644
Jess Askey-260644
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: 1
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
ChrisTaylor
ChrisTaylor
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 1882
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?

Chris

Blog:- chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
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