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 Wednesday, August 2, 2006 1:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:50 PM
Points: 20,584, Visits: 9,623
How do you scan this table to recreate the history of a row(s)? Or to rollback a group of transaction?
Post #299104
Posted Wednesday, August 2, 2006 3:16 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, January 22, 2011 12:01 PM
Points: 702, 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
Post #299122
Posted Wednesday, August 2, 2006 4:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:44 AM
Points: 134, Visits: 178
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 ?
Post #299130
Posted Wednesday, August 2, 2006 7:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 21, 2012 10:35 AM
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.

 

Post #299178
Posted Friday, August 4, 2006 8:15 AM
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

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

Post #299637
Posted Friday, August 4, 2006 8:31 AM
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

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

Post #299646
Posted Friday, August 4, 2006 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 21, 2012 10:35 AM
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.

 

Post #299650
Posted Friday, August 4, 2006 8:55 AM
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

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.

Post #299657
Posted Tuesday, August 22, 2006 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 27, 2007 4:11 PM
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
Post #303266
Posted Monday, May 12, 2008 8:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:30 PM
Points: 398, Visits: 1,735
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
Post #498853
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse