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.
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.
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 2005Version 8.0.50727.42 (RTM.050727-4200)Microsoft .NET FrameworkVersion 2.0.50727
Installed Edition: Professional
Microsoft Visual Basic 2005 77626-009-0000007-41371Microsoft Visual Basic 2005
- The author
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.
