I am an utter newbie to data auditing and temporal objects, but I have a project that would seem to benefit from using the Temporal Object pattern that Fowler mentions here: http://www.martinfowler.com/ap2/temporalObject.html

I think that down the road, my users will benefit from being able to reconstruct the history of objects in my table from time to time, but I'm unclear on the best way to capture that information. On the one hand, it seems that LLBLGen would likely not work well out of the box if there were never deletes/updates, but inserts with different time stamps. (If I just used one table for everything).

An alternative might be to create 2 tables for all temporal objects, one for current data, and one for legacy. From there I would use LLBL for the current data, and set up triggers to automatically update the temporal data when the other table changes.

My last thought would be to have a single audit table that maintains some fields (table name/table id) and then an xml field representing the data values. Functionally, this sounds similar, but more needlessly complex than #2.

So then what about archival/deletion of the temporal data? Are there any general rules/thoughts on keeping the size of this database from ballooning out of control, or just becoming needlessly unwieldy? I'm thinking something as simple as deleting the temporal data when the current data has been gone for X amount of time. Any other better ways to do this?

Sorry this wasn't a specific question as much as a general plea for guidance...

i once wrote a billing system. it tracked billable items (vehicles, licenses, aircraft, etc). Billable items had installments, e.g. some were paid monthly, quarterly etc, and installments could change. any changes to an installment could cause the current bill to change. installments from previous billing cycles could be altered as well.

the point is that every installment that the system was tracking for a given item could impact the current amount due for the current installment. all changes to installments had to be tracked.

i acheived this with a trigger. basically i had an installment table and an installment history table. prior to comitting a change for the current installment, i would put the a copy of the current installment into the installment history table using the trigger.

this approach was a pure db solution and it worked well except when business logic kept gettng added to the trigger, but i was long gone by then and was just acting as a consultant to the company, and to this day, they are still using the system and the trigger (albeit , heavily modified.)

looking back on it today, and seeing what llblgen can do, one would think that if an installment was an entity, and there was also an installment history entity, that, using the adapter patter, you could fetch an installment, create a new installment history entity, associate the new installent history entity to the installment entity being updated, then add the installment entity to a unit of work object and commit the changes. The result would end up with the current installment being updated and a new installment history record being created from the original installment.

hope this makes sense and gives you a bit of direction.

Cheers

Hey thanks for the reply DD. I would certainly prefer to go w/ CoolJ for everything, so w/ your line of thinking, I can think of two ways to do this.

Suppose I have an Order/Order History table, each with CoolJ objects associated w/ them.

Option 1: Use an OrderManager class that resides in the business layer that exposes SaveOrder(Order). This method will save to both the order and orderhistory objects automatically. This seems to fit your Unit of Work pattern. The concern here is that it really doesn't force the user to go that route. If someone picks up the code unaware of this need, they could easily create an order, and use the DataAdapter object to persist it, right?

Option 2: Alter the DataAdapter template such that it checks for a certain property, and if found, adjusts the way in which it persists the order such that an orderhistory record is also inserted. Essentialy, this solves the problem in #1 by moving the functionality to the Adapter itself. However, this would require maintaining a flag on the Order property to indicate that actions involving it should be tracked. The data adapter would then need to know which object is responsible for this history, and create, populate, and save it. In addition to the difficulty, I am concerned that this would involve adding business logic into the data layer. (If what you said about your triggers becoming laden w/ biz logic is true, then theoretically this would be also).

Basically, Option 1 seems dangerous, and Option 2 seems difficult, time consuming, and possibly wouldn't avoiding the business logic 'creepage' you warned of.

I guess I'm leaning toward Option 1, as I suppose if someone is determined to avoid your business logic, there's no telling what havoc they might wreak. Is there a more elegant solution that I am missing though? Would you mind providing some examples of biz logic that wound up finding its way into your triggers so I know what to look out for?

Thanks Dog.

hehe CoolJ

You can also opt for a derived class of DataAccessAdapter, which you use to persist the objects, and you inject an object in the instance of that derived class which has the knowledge of which objects to have history info. (you can also inject that in the order entity, through an include template for example).

We use triggers as well for 'history' functionality, not because we wouldn't like it in our business tier, but simply because it is the only way to guarantee that you get all changes, anyone developer or DBA with access to QA can change data on the fly and we need to track them as well as what an end user does.

John

Business logic that made it into the triggers were big chunks of business rules seperated by control of flow logic. The point of each installment was to track amount due for a time period for an item having a taxable value. Business logic that ended up being in there were different functions to calculate tax based on item type, pro-rating rules, etc etc.

At the end of the day things got very ugly.

I dont think that youre going to be able to find a one solution fits all scenario for tracking history. If you absolutely want to guarantee that every update creates a new histry record, then a trigger is the way to go.

If you force all developers to use a business object that encapsulates the history creation method, then you are a bit safer than in option 1 and you control how and when the derived adapter acheives the creation of history items. Deriving from DataAccessAdapter isnt a big deal really.

That being said, if the consumer of the data isnt using your business object, then it is probable that items could get changed and no history is created.

Another thing that I have done in the past when dealing with multiple line of business apps using the same data source is to track which system did the update, by putting a system identifier field on the entity. At least that way, you would know what codebase to check, in the event that a history record is not created, or created errantly.

Thank you for all of the feedback guys. This forum is awesome. I'm thinking that I'll probably experiment with something along the inheriting the dataaccessadapter line, and if it seems easy enough go that way. Otherwise, perhaps I'll explore the trigger angle. I understand the reasons that it might be used, but I don't think that they will apply in our particular situation.

Thanks again all.

I've had some time to reflect on all of the input I've received, and I've got some of my own now:

I tried to extend DataAccessAdapter in my BL, and create the audit fields in OnSaveEntity and OnDeleteEntity event handlers. I thought a simple solution would be to just serialize the entire entity to XML using built-in CoolJ functionality. This turned out to be really slow since it was serializing the entire object graph. It was also very large due to issues w/ inheritance that necessitated the use of Frans' verbose xml output.

As for my concern regarding users being able to circumvent my auditing efforts, well the major problem there would indeed be manual database edits. (And I'd probably be the primary offender. )

So what I've found is a CodeSmith trigger generator here: http://community.codesmithtools.com/forums/post/3748.aspx

This creates the audit table, and adds the data to it automatically. Since each row represents only 1 column change in a generic fashion, I have a database structure agnostic solution. I can use the TransactionId to obtain all of the changes that occurred in that entire transaction, which helps since I can only see 1 change/row. I haven't checked the speed impact of this, but I think that if its reasonable, that I would have a solution that is part of my personal framework (unrelated/orthogonal to my biz logic). It shouldn't be too difficult to reconstruct the entities in previous states. In fact I should be able to make the reconstitution generic as well if I make sure I have a standardized Table -> Entity naming scheme.

Any thoughts/concerns on this approach would of course be very welcome before I get too far down this path. Thanks all!

**Update: http://community.codesmithtools.com/forums/post/3748.aspx I like the 2 table approach a litle more actually, and the use of sql_variant seems to be a better choice. The code I think (not tested) will auto-avoid auditing text/ntext fields since they won't fit anyway. (The first one doesn't). I will probably add the TransactionID to this trigger so that I'll understand when multiple tables were altered at once.

jeffdeville wrote:

As for my concern regarding users being able to circumvent my auditing efforts, well the major problem there would indeed be manual database edits. (And I'd probably be the primary offender. )

If this is your main concern, then there is really no option outside of triggers (at least that I can think of).

Is part of auditing having the ability to tell WHO made the change, and not just WHAT the change was? If so, another downside (from my perspective) is that you'll have to have SQL Server logins for all users who could make changes.

So my initial advice would be: don't let users edit data directly in the database. Create an interface for them to do it. (Of course, you have to make sure you use the interface yourself!)

jeffdeville wrote:

So what I've found is a CodeSmith trigger generator here: http://community.codesmithtools.com/forums/post/3748.aspx

I haven't looked at the link or the details, but from your (snipped) description, it sounds like it might not be efficient. If the code has to check each field for changes on each save (I assume this is how it would have to work), there could be a lot of overhead.

If the other choice is using a trigger to write the whole row to a table before it gets saved, I would recommend that instead.

jeffdeville wrote:

I thought a simple solution would be to just serialize the entire entity to XML using built-in CoolJ functionality.

I'm not clear on what serialization has to do with it. You save the history to disk and not to the database?

If you really do have to support tracking changes that are made in the database directly, I think you have no choice (you have to use triggers).

But if not, overriding the DataAccessAdapter will make your life MUCH easier.

Phil

Thank you for the input Phil. A few follow up questions for you:

It sounds as if you think the trigger is probably pretty inefficient. I'm not sure what the performance penalty is, but I can see how determining what has actually changed would be a performance penalty. It sounds like you think a faster solution would be to just copy the contents of the changed row without checking what values had changed. I have 2 concerns that you may be able to clarify/alleviate:

1. My primary concern here is that it sounds as if this means I'd need to double the number of tables I maintain. (Adding fields to indicate who and when a change took place) Is this correct, or have I misunderstood you? This implies that changes to my data model will have to be reflected in changes to my audit data model as well as to the audit triggers. Certainly not the end of the world, but it would be some maintenance overhead.
2. If I have some tables w/ large rows, and I have to copy each row, then an 8000kb row that only changes an int value would result in writing out the whole row again. It seems that this increase in data writing might to some degree offset the processing penalty of the previous approach. Of course, I have no idea how much.

To be honest, I'm really not too worried about just using dataaccessadapter for auditing. I just need to figure out how to do it efficiently. I could essentially replicate what is being done in either trigger strategy pretty easily, but it would mean another round trip to the database, which would seem to be less efficient than the dirty column trigger, while sacrificing the ability to capture changes from all sources. Is there a reason I should go with overriding DAA over CodeSmithed triggers that do the same thing that you could point out? I'd definitely side w/ the centralized, reusable code of DAA were it not for the fact that CodeSmith and the work of others have largely reduced the code maintenance problem for triggers.

Thanks for your input Phil. It is definitely appreciated.

jeffdeville wrote:

Thank you for the input Phil. A few follow up questions for you:

It sounds as if you think the trigger is probably pretty inefficient. I'm not sure what the performance penalty is, but I can see how determining what has actually changed would be a performance penalty.

Yep, that's pretty much all I meant.

jeffdeville wrote:

It sounds like you think a faster solution would be to just copy the contents of the changed row without checking what values had changed. I have 2 concerns that you may be able to clarify/alleviate:

1. My primary concern here is that it sounds as if this means I'd need to double the number of tables I maintain. (Adding fields to indicate who and when a change took place) Is this correct, or have I misunderstood you? This implies that changes to my data model will have to be reflected in changes to my audit data model as well as to the audit triggers. Certainly not the end of the world, but it would be some maintenance overhead.

Triggers can absolutely be a maintenance nightmare--this is why I only use them in very specific, controlled circumstances. I'm sure you've heard (or experienced) horror stories about triggers gone bad. I was under the impression that the triggers would be maintained by code generation via CodeSmith. Maybe you only meant the "per field" triggers would work that way.

As far as maintaining the tables: yes, that's one downside. You have to keep the tables uniform. Any change to the base table would require a change to the history/audit table. I guess that can be a huge problem if you make a lot of changes to you production tables, and you have a lot of tables that require audit tracking. If you are going to do it this way, I recommend that you implement and test this functionality as the last thing you do before you push your database to production, since there will tend to be a lot of model changes during the development cycle.

In general, keeping things uniform is a major benefit. If each of your history tables is named <tablename>_history for example, it makes it easier to write queries (against sysobjects) to ensure that the base and history tables are in synch. If there are a lot of these tables, investing some time in writing these scripts would be necessary, or at least highly beneficial. In the past I've done this by hand, but I'm pretty sure it's possible.

jeffdeville wrote:

1. If I have some tables w/ large rows, and I have to copy each row, then an 8000kb row that only changes an int value would result in writing out the whole row again. It seems that this increase in data writing might to some degree offset the processing penalty of the previous approach. Of course, I have no idea how much.

I can see your point. I can't be sure how much more efficient it would be (or even IF it's more efficient) without actually testing it. It seems to me that just writing a row would be more efficient than checking one row vs. another (and writing several, smaller rows as a result), even though the latter would all happen inside the database. There are a lot of factors, though, and what is more efficient in one case may be less so in another.

One thing I will say is that it's very easy to query the "per row" design and re-assemble the history of what happened. I'm not sure how easy it would be to query the "per column" design.

jeffdeville wrote:

To be honest, I'm really not too worried about just using dataaccessadapter for auditing. I just need to figure out how to do it efficiently. I could essentially replicate what is being done in either trigger strategy pretty easily, but it would mean another round trip to the database, which would seem to be less efficient than the dirty column trigger, while sacrificing the ability to capture changes from all sources. Is there a reason I should go with overriding DAA over CodeSmithed triggers that do the same thing that you could point out? I'd definitely side w/ the centralized, reusable code of DAA were it not for the fact that CodeSmith and the work of others have largely reduced the code maintenance problem for triggers.

I have to admit that I always start with a premise of NOT using triggers unless I have to. You make some good arguments in the other direction, for sure (changes from all sources and fewer round trips).

I think the major benefits of using the DAA method are:

1. It's easy to code and maintain.
2. It affords a high level of control (if one entity needs some special fields, it's very easy to handle in code).
3. It allows customization of the whole pattern. For example, I usually pass an application-level UserId into the constructor of the derived DAA. I can then use this ID to write into my history table as the audited user. I actually also use this for CreatedByUser and LastUpdatedByUser, two fields I have on all my non-history entities.

jeffdeville wrote:

Thanks for your input Phil. It is definitely appreciated.

No sweat. I want to (eventually) write an article on how to do basic auditing with LLBLGen, so it's an interesting topic to me. Plus I had some free time today (a rarity).

Phil

I would reccomend the derived DAA approach too. I used it successfuly with a custom serialiser to store per-column changes against heirachial workfow tasks.

Was simply a case of creating column name and entity name tables which were referenced by a generic history table which in turn referenced the task and then populating that table in the OnSaveEntityComplete method.

Here's some code:

public class LoggingAdapter : DataAccessAdapter
    {
        private static ArrayList _ExcludedEntityTypes = new ArrayList(); 


        static LoggingAdapter()
        {
            _ExcludedEntityTypes.AddRange(new Type[] {typeof(FieldChangeLogEntity),
                                                      typeof(ActivityLogEntity),
                                                      typeof(LUFieldNameEntity),
                                                      typeof(LUEntityTypeEntity),
                                                      typeof(UIPTaskEntity),
                                                      typeof(LUActivityEntity),
                                                      typeof(UserEntity),
                                                      typeof(ActivityParametersEntity),
                                                      typeof(ImageEntity),
                                                      typeof(CommunicationEntity)});
        }

        /// <summary>
        /// Store a field change record for appropriate entities, using a minimal serialiser to save space
        /// </summary>
        /// <param name="saveQuery"></param>
        /// <param name="entityToSave"></param>
        public override void OnSaveEntityComplete(SD.LLBLGen.Pro.ORMSupportClasses.IActionQuery saveQuery, SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToSave)
        {
            if(_ExcludedEntityTypes.Contains(entityToSave.GetType()))
                return;
            UnitOfWork2 uow = new UnitOfWork2();
            //No context to log against, return
            if(CSLA.ApplicationContext.GlobalContext["ParentActivity"] == null)
                return;
        
            for(int i = 0;i != entityToSave.Fields.Count;i++)
            {
                IEntityField2 field = entityToSave.Fields[i];
                if(field.IsChanged)
                {
                    LUFieldNameEntity fieldName = LUFieldNameManager.Operations.CreateOrFetch(field.Name);
                    LUEntityTypeEntity entityType = LUEntityTypeManager.Operations.CreateOrFetch(entityToSave.GetType().Name);
                    FieldChangeLogEntity logEntry = FieldChangeLogEntity.Create();

                    logEntry.ActivityLog = CSLA.ApplicationContext.GlobalContext["ParentActivity"] as ActivityLogEntity;
                    logEntry.LUFieldName = fieldName;
                    logEntry.LUEntityType = entityType;
                    

                    //serialise value using space efficient serialisation 
                    using(MemoryStream str = new MemoryStream())
                    {
                        BinaryWriter writer = new BinaryWriter(str);
                        AltSerialization.SerializeByType(writer,field.CurrentValue); 
                        
                        logEntry.Value = str.GetBuffer();
                    }

                    uow.AddForSave(logEntry);
                }
            }

            DataAccessAdapter adapt = new DataAccessAdapter();

            uow.Commit(adapt,true);

        }

    }

I thought somebody else should weigh in on the side of triggers... One of the things that I love about a trigger for audit purposes is that it catches ALL changes to the database, regardless of source. For instance: what if an end-user has the ability to connect to the database directly with a tool like Access or Excel, or what if a dba is running an UPDATE in query analyser?

Here is an example of the trigger based approach that I use:

http://www.nigelrivett.net/AuditTrailTrigger.html

This approach uses one generic audit table with the following columns:

cType char(1), TableName varchar(12 , PK varchar(1000), FieldName varchar(12 , OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(12

The triggers are generic too, the only developer edit required is a hardcoded table name in each trigger. So there is zero maintenance when you modify a tables schema.

Thank you again Phil. Based on your recommendation, I think I'm going to go w/ the DAA method rather than triggers. Speed isn't going to be a critical issue for us I don't think. I do have a few implementation questions. I can probably get all of this eventually through trial and error, so you don't need to feel obligated to reply, but you've been a wealth of info so far, so... :-)

So here's the goals: - Orthogonal to the solution (w/ dependency on llbl) sorta The problem w/ being completely independent is that it's possible (and in my case certain) that some functionality be provided by the audit tables. In my case, users want to be able to see a quick list of what they've been working on recently. The audit tables are perfect for this, but it immediately kills orthogonality. I'll explain how I'm trying to mitigate in the code section

• Audits everything
• Provider pattern using constructor dependency injection to identify WHO makes the changes

And the strategy for implementation so far:

**Database: ** Create 2 tables AuditEntry, and AuditEntryChanges that so far look like this:

GO CREATE TABLE [dbo].[tbl_DAA_AuditEntry] ( [AuditId] [int] IDENTITY (1, 1) NOT NULL , [ChangeType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateModified] [datetime] NOT NULL , [ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EntityType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PrimaryKeyField] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PrimaryKey] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO

CREATE TABLE [dbo].[tbl_DAA_AuditEntryChanges] ( [AuditId] [int] NOT NULL , [FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OldValue] [sql_variant] NULL , [NewValue] [sql_variant] NOT NULL ) ON [PRIMARY] GO

LLBL - Adapter, .net 2.0 There seem to be a few strategies here:

• Adapter Template futzing
• Override adapter in a separate project
• AOP via spring/castle etc Template Alteration I'll be honest: I can't figure out the template stuff w/ llbl. It's clearly very powerful, but as of yet, not well documented, so I'll pass on this one due to complexity.

New project / override adapter I can make a separate project that only includes the 2 tables/entities mentioned above (though I foresee these tables residing in the same database). I'll also override the adapter here. The actual auditing work will take place in the overriden adapter class. There are 2 problems here: 1- I don't know if the adapter will even work for entities in a separate project. I'll just have to test it. 2- I want the audit table to know who is making each change, which means that I need to provide some mechanism to determine this information. I'd go w/ a constructor dependency injection that would look something like this:

public interface ISecurityInfoProvider
{
   string UserName { get; }
}

This object would have to be provided to the dataaccessadapter. (Maybe I have to alter the templates after all?) From here, the auditing would use this object when inserting the audit fields.

AOP I like the concept of advice that is completely separate from everything else for logging, but it seems like overkill, since I'm already acknowledging a dependency on llbl, and dataaccessadapter is the only llbl piece to alter anyway. On the other hand, I can see how it might be used with the overridden adapter project as a way of injecting new business rules. (ie: If I don't want to log certain EntityTypes, I might add this app-specific logic via AOP) It could be handled via Constructor injection as well though.

There is another large problem regardless of which direction I go: The ability to use this data is not available to my application. This is a big one. The only way I can think to handle this is to create a manager class that abstracts the details from the real app. I'm thinking there might be an AuditCriteria class, and an AuditManager class that are used to provide info about what audit info is of interest. So if in my case I wanted to know the last 3 CustomerEntity objects I've edited, it might look like this:

AuditCriteria criteria = new AuditCriteria();
criteria.User = "Jeff";
criteria.EntityType = EntityType.CustomerEntity.ToString();
criteria.EntitiesToReturn = 3;
criteria.SortBy = SortBy.DateDescending;

AuditManager auditManager = new AuditManager();
List<string> idsOfCustomersIEditedRecently = auditManager.FetchIDs(criteria);

From here, my code must extract the keys returned, and pull the entities from the database (separate call).

This allows me to not care too much about the innerworkings of the audit functionality, but it's still leaving a nasty taste in my mouth, as there would be a lot of functionality to pack in here.

Is there an alternative approach I'm overlooking here?

Hello Eric, thank you for the input. The first codesmith template I linked to is based on Nigel's audit schema actually. One of the difficulties I had here was how to track who was making the changes. For instance, in my web apps, there is only 1 account to the database, and the functionality is limited through an application security mechanism. The only workaround I could think of was to add a LastUpdatedBy field to each table, and use this field to populate the audit field. (The codesmith template provided this facility). This struck me as being a little counter to my goal of disentangling the auditing from the datamodel though.

The other concern I had w/ this was easily fixable, and that is the use of nvarchar(1000). sql_variant seemed like a better choice since it would expand automatically. Either way you're in trouble if the trigger tries to audit a text/ntext field, or a field whose before/after values exceeded 8k. Of course, this problem exists for all solutions I've looked into.

The second link at the top just avoids auditing large fields. I was in the middle of combining these approaches when I decided to just override the adapter due to the security problem. Still, the ability to use these triggers w/ codesmith could easily replace the adapter functionality if there is a simple way to avoid the security problem. In fact, the result would then be llbl independent.

eric_the_web wrote:

I thought somebody else should weigh in on the side of triggers... One of the things that I love about a trigger for audit purposes is that it catches ALL changes to the database, regardless of source. For instance: what if an end-user has the ability to connect to the database directly with a tool like Access or Excel, or what if a dba is running an UPDATE in query analyser?

Here is an example of the trigger based approach that I use:

http://www.nigelrivett.net/AuditTrailTrigger.html

This approach uses one generic audit table with the following columns:

cType char(1), TableName varchar(12 , PK varchar(1000), FieldName varchar(12 , OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(12

The triggers are generic too, the only developer edit required is a hardcoded table name in each trigger. So there is zero maintenance when you modify a tables schema.

I love it when other people have written the code for me. Thanks Ryan. Could you provide a little more input into what the auditing portion of your database looks like?

Update: Hey Ryan, would you mind telling me what SerializeByType does?

Hey Jeff,

My current approach tends to be more toward the "extra table per audited entity" approach, so I'm not sure my own code would be that helpful to you. (If you do want it, let me know and I'll post it or email me at psandler70 AT yahoo.com and I'll send it you you)

One thing that has been mentioned twice (once by you and now in Ryan's code) is serializing the fields. I'm not clear on why you would need this. For BLOBs?

Phil

Phil, if you could post the code, that'd be great. I'd ask you via email, but I'm sure it's a resource that others could benefit from as well. I'm sure not everyone will agree with my approach anyway.

As for serializing the fields, I think it depends on what is being serialized. If you're serializing one col at a time, then the only benefit I can think of would be if you were trying to limit the number of entries that would exceed the 8k boundary. It's not really something I care about personally, as I'd rather be able to read the data directly, but any field >4k would be too big to store.

My first Adapter override attempt centered on trying to serialize the entire object graph of just certain entity types. This was too painful in terms of processing time. Now that I think about it though, it might not be too awful if I try to just serialize the Fields collection... I wonder if that's possible. If so, It would provide me a mechanism by which I could directly reconstitute the entity w/ llbl.... hmm... Thanks for asking that question.

psandler wrote:

Hey Jeff,

My current approach tends to be more toward the "extra table per audited entity" approach, so I'm not sure my own code would be that helpful to you. (If you do want it, let me know and I'll post it or email me at psandler70 AT yahoo.com and I'll send it you you)

One thing that has been mentioned twice (once by you and now in Ryan's code) is serializing the fields. I'm not clear on why you would need this. For BLOBs?

Phil

OK, just an update. DataAccessAdapters are only good for the project they were generated for. This means that all auditing logic will need to exist in the project itself. Not as independent as I'd hoped, but it should become fairly flexible if I ever figure out how to integrate into the generation process (which until documentation exists is a fairly low priority for me.)

Ok, here's my derived DataAccessAdapter code. You can see that there are certain assumptions about how the auditing tables are set up, how the primary keys work, and how all non-auditing tables are set up. Let me know if anything needs clarification.

One other thing to note. If you have a lot of related tables that are audited, it's helpful to ensure that ALL rows in the table that's being audited have at least a "Create" row in their audit table. In other words, if there is existing data before you start auditing, write a SQL script that creates audit rows for each of the source rows when when the auditing functionality first goes live. This makes it much easier to join the related audit tables together when you query them (in much the same way their counterpart tables are joined to gether). I hope that made sense.

using System;
using System.Collections.Generic;
using System.Text;
using myproject.dal.DatabaseSpecific;
using SD.LLBLGen.Pro.ORMSupportClasses;
using myproject.dal.EntityClasses;

namespace myproject.dao.LBLDerived
{
    internal class myprojectAdapter: DataAccessAdapter
    {
        private int _userId;

        internal myprojectAdapter(int userId)
        {
            _userId = userId;
        }

        public override void OnDeleteEntity(SD.LLBLGen.Pro.ORMSupportClasses.IActionQuery deleteQuery, SD.LLBLGen.Pro.ORMSupportClasses.IEntity2 entityToDelete)
        {
            if (Convert.ToInt32(((IEntityField2)entityToDelete.Fields.PrimaryKeyFields[0]).CurrentValue) != 0)
            {
                SaveAuditEntity(entityToDelete, "Deleted");
            }
            base.OnDeleteEntity(deleteQuery, entityToDelete);
            
        }
        
        public override void OnSaveEntityComplete(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            string auditSaveType = string.Empty;

            if (entityToSave.IsDirty)
            {
                if (entityToSave.IsNew)
                {
                    auditSaveType = "Created";
                }
                else
                {
                    auditSaveType = "Updated";
                }
                SaveAuditEntity(entityToSave, auditSaveType);
            }
            base.OnSaveEntityComplete(saveQuery, entityToSave);
        }

        /// <summary>
        /// Overrides OnBeforeEntitySave.  This allows us to "interrupt" the save of an entity to populate audit fields.
        /// </summary>
        /// <param name="entitySaved"></param>
        /// <param name="insertAction"></param>
        public override void OnBeforeEntitySave(IEntity2 entitySaved, bool insertAction)
        {
            string auditSaveType = string.Empty;

            if (entitySaved.IsNew)
            {
                entitySaved.SetNewFieldValue("CreatedByUserId", _userId);
                entitySaved.SetNewFieldValue("CreatedDate", DateTime.Now);
            }

            if (entitySaved.IsDirty)
            {
                entitySaved.SetNewFieldValue("LastUpdatedByUserId", _userId);
                entitySaved.SetNewFieldValue("LastUpdatedDate", DateTime.Now);
            }
            base.OnBeforeEntitySave(entitySaved, insertAction);
        }

        /// <summary>
        /// This method checks to see if an entity that is being saved has a audit table
        /// that also must be updated, and updates it if it does.
        /// </summary>
        /// <param name="entityToSave">The entity that is being saved</param>
        /// <param name="action">The action being done on the base entity.  
        /// "Deleted" = Delete
        /// "Created" = Create
        /// "Updated" = Update.
        /// </param>
        private void SaveAuditEntity(IEntity2 entityToSave, string action)
        {
            IEntity2 auditEntity = null;
            

            if (entityToSave is FooEntity
            {
                auditEntity = new FooAuditEntity();
            }
            else if (entityToSave is BarEntity)
            {
                auditEntity = new BarAuditEntity;
            }
            //else if (etc.)
            if (auditEntity != null)
            {
                //NOTE: this is a "standard" dataaccess adapter, NOT the derived one (which is the class we are in)
                DataAccessAdapter adapter = new DataAccessAdapter();

                SetAuditEntityFields(entityToSave, auditEntity, action);
                adapter.SaveEntity(auditEntity, false, null, false);
            }
        }

        private void SetAuditEntityFields(IEntity2 baseEntity, IEntity2 auditEntity, string action)
        {
            //audit tables have all the fields of the original
            //plus four fields in positions 0-3:
            //identity/PK field
            //action date,
            //action user,
            //action type (delete, update, create)

            const int FIELD_OFFSET = 4;

            //loop through all the fields of the entity, setting the audit field value to the original (database) field value
            for (int x = 0; x < baseEntity.Fields.Count; x++)
            {
                //if the field we are on is the update date field or the created date field AND we are creating a new record, update these directly
                if ((x == baseEntity.Fields.Count - 1 || x == baseEntity.Fields.Count - 3) && action == "Created")
                {
                    auditEntity.SetNewFieldValue(x + FIELD_OFFSET, DateTime.Now);
                }
                else
                {
                    if (baseEntity.Fields[x].IsChanged == false && baseEntity.Fields[x].IsNull)
                    {
                        auditEntity.SetNewFieldValue(x + FIELD_OFFSET, null);
                    }
                    else
                    {
                        auditEntity.SetNewFieldValue(x + FIELD_OFFSET, baseEntity.Fields[x].CurrentValue);
                    }
                }
            }

            //now the three additional fields (the one at position 0 is an ID/auto-increment field)
            auditEntity.SetNewFieldValue(1, _userId);
            auditEntity.SetNewFieldValue(2, DateTime.Now);
            auditEntity.SetNewFieldValue(3, action);
        }       
    }
}

More of my ignorance can be found above when I talk about being able to read the audit fields with my own eye, and at the same time advocating sql_variant columns. You can't just view variant fields. It just shows <BINARY>. Bummer.

Thanks Phil. I'm looking at your code now.

Ok all, this is what I have so far to capture the audit information

AuditAdapter.cs

using System;
using System.Collections.Generic;
using System.Text;
using sap.valuecollateral.dl.DatabaseSpecific;
using sap.valuecollateral.dl.EntityClasses;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace sap.valuecollateral.bl
{
    public class AuditAdapter : DataAccessAdapter
    {
        ISecurityInfoProvider _securityProvider;
        ICompressionProvider _compressionProvider;
        List<string> _entityTypesToIgnore;

        #region Constructors
        public AuditAdapter() : this(null, null, null) { }
        public AuditAdapter(ISecurityInfoProvider securityProvider) : this(securityProvider, null, null){}

        public AuditAdapter(ICompressionProvider compressionProvider) : this(null, compressionProvider, null) { }

        public AuditAdapter(ISecurityInfoProvider securityProvider, ICompressionProvider compressionProvider) : this(securityProvider, compressionProvider, null) { }

        public AuditAdapter(ISecurityInfoProvider securityProvider, ICompressionProvider compressionProvider, List<string> entityTypesToIgnore)
        {
            _securityProvider = securityProvider == null ? new WebSecurityProvider() : securityProvider;
            _compressionProvider = compressionProvider == null ? new NoChangeCompressionProvider() : compressionProvider;
            _entityTypesToIgnore = entityTypesToIgnore == null ? new List<string>() : entityTypesToIgnore;
        }
        #endregion

        protected override void OnSaveEntityComplete(IActionQuery saveQuery, IEntity2 entityToSave)
        {
            base.OnSaveEntityComplete(saveQuery, entityToSave);

            if (IgnoreThisEntity(entityToSave))
                return;

            AuditEntryEntity aEntity = CreateAuditEntry(entityToSave, entityToSave.IsNew ? "I" : "U");
            

            foreach (IEntityField2 field in entityToSave.Fields)
            {               
                if (field.IsChanged)
                {
                    AuditEntryChangesEntity changedField = new AuditEntryChangesEntity();
                    aEntity.AuditEntryChangesCollection.Add(changedField);
                    changedField.FieldName = field.Name;
                    changedField.OldValue = _compressionProvider.CompressField(field.DbValue);
                    changedField.NewValue = _compressionProvider.CompressField(field.CurrentValue);
                }
            }
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                if (!adapter.SaveEntity(aEntity, false, true))
                    throw new ORMException("AuditTrail could not be saved.");
            }
        }

        protected override void OnDeleteEntityComplete(IActionQuery deleteQuery, IEntity2 entityToDelete)
        {
            base.OnDeleteEntityComplete(deleteQuery, entityToDelete);
            if (IgnoreThisEntity(entityToDelete))
                return;

            AuditEntryEntity aEntity = CreateAuditEntry(entityToDelete, "D");

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.SaveEntity(aEntity, false, false);
            }
        }

        private AuditEntryEntity CreateAuditEntry(IEntity2 entity, string action)
        {
            AuditEntryEntity aEntity = new AuditEntryEntity();
            aEntity.ChangeType = action;
            aEntity.ModifiedBy = _securityProvider.UserName;
            aEntity.EntityType = entity.GetType().Name;
            SetPrimaryKeys(aEntity, entity);
            return aEntity;
        }

        private bool IgnoreThisEntity(IEntity2 entity)
        {
            return _entityTypesToIgnore.Contains(entity.GetType().Name);
        }
        
        private void SetPrimaryKeys(AuditEntryEntity aEntity, IEntity2 entity)
        {
            for (int primaryKeyIndex = 0; primaryKeyIndex < entity.PrimaryKeyFields.Count; primaryKeyIndex++)
            {
                aEntity.Fields["PrimaryKeyName" + primaryKeyIndex].CurrentValue = 
                    entity.PrimaryKeyFields[primaryKeyIndex].Name;
                aEntity.Fields["PrimaryKeyValue" + primaryKeyIndex].CurrentValue =
                    entity.PrimaryKeyFields[primaryKeyIndex].CurrentValue.ToString();
            }
        }
    }
}

ICompressionProvider.cs

using System;
using System.IO;
using System.Collections.Generic;
using System.Text;

namespace sap.valuecollateral.bl
{
    public interface ICompressionProvider
    {
        object CompressField(object input);
    }
    public class NoChangeCompressionProvider : ICompressionProvider
    {
        #region ICompressionProvider Members

        public object CompressField(object input)
        {
            return input;
        }

        #endregion
    }
}

ISecurityInfoProvider

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Web.Security;
using System.Security;

namespace sap.valuecollateral.bl
{
    public interface ISecurityInfoProvider
    {
        string UserName { get;}
    }

    public class WebSecurityProvider : ISecurityInfoProvider
    {
        #region ISecurityInfoProvider Members

        public string UserName
        {
            get 
            {
                if (Thread.CurrentPrincipal as RolePrincipal == null)
                {
                    RolePrincipal rp = new RolePrincipal(System.Security.Principal.WindowsIdentity.GetCurrent());
                    Thread.CurrentPrincipal = rp;
                }
                return Thread.CurrentPrincipal.Identity.Name;
            }
        }

        #endregion
    }
}

And this is the nasty/partially complete code is what I have so far to retrieve it... Suggestions in particular here are welcome. It's not really ready for prime time, but should be enough to let everyone see what I'm trying to do, and hopefully suggest improvements.

AuditCriteria

using System;
using System.Collections.Generic;
using System.Text;

namespace sap.valuecollateral.bl
{
    public class AuditCriteria : AbstractCriteria
    {
        public AuditCriteria()
        {
            SearchType = SearchTypeEnum.ConstructHistory;
        }

        public enum SearchTypeEnum
        {
            RecentlyModified, ConstructHistory
        }
        private SearchTypeEnum _searchType;
        public SearchTypeEnum SearchType
        {
            get { return _searchType; }
            set { _searchType = value; }
        }

        public struct PrimaryKeyStruct
        {
            public string Name;
            public string Value;
            public PrimaryKeyStruct(string name, string value)
            {
                Name = name;
                Value = value;
            }
        }
        string[] _entityType;
        public string[] EntityType
        {
            get { return _entityType; }
            set { _entityType = value; }
        }
        PrimaryKeyStruct[] _primaryKeys;

        public PrimaryKeyStruct[] PrimaryKeys
        {
            get { return _primaryKeys; }
            set { _primaryKeys = value; }
        }
        string[] _modifiedBy;

        public string[] ModifiedBy
        {
            get { return _modifiedBy; }
            set { _modifiedBy = value; }
        }
        DateTime _begin, _end;

        public DateTime EndDate
        {
            get { return _end; }
            set { _end = value; }
        }

        public DateTime BeginDate
        {
            get { return _begin; }
            set { _begin = value; }
        }
        
    }
}

AuditManager

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using sap.valuecollateral.dl.DatabaseSpecific;
using sap.valuecollateral.dl.EntityClasses;
using sap.valuecollateral.dl.FactoryClasses;
using sap.valuecollateral.dl.HelperClasses;
using sap.valuecollateral.dl.RelationClasses;
using sap.valuecollateral.dl;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace sap.valuecollateral.bl
{
    public class AuditManager : IAuditManager
    {
        /// <summary>
        /// Goal is to return a list of the AuditEntries.  I won't check the session, since this is likely to change a lot.
        /// </summary>
        /// <param name="criteria"></param>
        /// <returns></returns>
        public EntityCollection<AuditEntryEntity> FetchCollection(AuditCriteria criteria)
        {
            EntityCollection<AuditEntryEntity> _auditEntries = new EntityCollection<AuditEntryEntity>(new AuditEntryEntityFactory());
            PrefetchPath2 prefetch = CreatePrefetch(criteria);
            RelationPredicateBucket bucket = CreatePredicate(criteria);
            SortExpression sort = new SortExpression(AuditEntryFields.DateModified | SortOperator.Descending);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(_auditEntries, bucket, 0, sort, prefetch);
            }
            return _auditEntries;
        }

        public IList<AuditCriteria.PrimaryKeyStruct[]> FetchRecentMultiKey(string entityType, string userName, int numToRetrieve)
        {
            RelationPredicateBucket bucket;
            SortExpression sort;
            SetUpFetchRecent(entityType, userName, out bucket, out sort);

            ResultsetFields fields = new ResultsetFields(6);
            fields[0] = AuditEntryFields.PrimaryKeyName0;
            fields[1] = AuditEntryFields.PrimaryKeyValue0;
            fields[2] = AuditEntryFields.PrimaryKeyName1;
            fields[3] = AuditEntryFields.PrimaryKeyValue1;
            fields[4] = AuditEntryFields.PrimaryKeyName2;
            fields[5] = AuditEntryFields.PrimaryKeyValue2;
            
            IDataReader reader = null; 
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                reader = adapter.FetchDataReader(fields, bucket, CommandBehavior.CloseConnection, numToRetrieve, sort, false);
                IList<AuditCriteria.PrimaryKeyStruct[]> primaryKeys = new List<AuditCriteria.PrimaryKeyStruct[]>();
                while (reader.Read())
                {
                    AuditCriteria.PrimaryKeyStruct[] keyStructArray = new AuditCriteria.PrimaryKeyStruct[3];
                    keyStructArray[0] = new AuditCriteria.PrimaryKeyStruct(reader[0].ToString(), reader[1].ToString());
                    keyStructArray[1] = new AuditCriteria.PrimaryKeyStruct(reader[2].ToString(), reader[3].ToString());
                    keyStructArray[2] = new AuditCriteria.PrimaryKeyStruct(reader[4].ToString(), reader[5].ToString());
                    primaryKeys.Add(keyStructArray);
                }
                reader.Close();
                return primaryKeys;
            }
        }

        public IList<string> FetchRecentSingleKey(string entityType, string userName, int numToRetrieve)
        {
            RelationPredicateBucket bucket;
            SortExpression sort;
            SetUpFetchRecent(entityType, userName, out bucket, out sort);

            ResultsetFields fields = new ResultsetFields(1);
            fields[0] = AuditEntryFields.PrimaryKeyValue0;

            IDataReader reader = null;
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                reader = adapter.FetchDataReader(fields, bucket, CommandBehavior.CloseConnection, numToRetrieve, sort, false);

                IList<string> keys = new List<string>();
                while (reader.Read())
                    keys.Add(reader[0].ToString());
                reader.Close();
                return keys;
            }
        }
        
        #region Private Methods

        private PrefetchPath2 CreatePrefetch(AuditCriteria criteria)
        {
            if (criteria.SearchType == AuditCriteria.SearchTypeEnum.RecentlyModified)
                return null;
            else
            {
                PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.AuditEntryEntity);
                prefetch.Add(AuditEntryEntity.PrefetchPathAuditEntryChangesCollection);
                return prefetch;
            }
        }

        private RelationPredicateBucket CreatePredicate(AuditCriteria criteria)
        {
            RelationPredicateBucket bucket = new RelationPredicateBucket();

            if (criteria.BeginDate != DateTime.MinValue)
                bucket.PredicateExpression.Add(AuditEntryFields.DateModified >= criteria.BeginDate);

            if (criteria.EndDate != DateTime.MinValue)
                bucket.PredicateExpression.AddWithAnd(AuditEntryFields.DateModified <= criteria.EndDate);

            if (!criteria.ArrayIsNullOrEmpty(criteria.EntityType))
                bucket.PredicateExpression.AddWithAnd(AuditEntryFields.EntityType == criteria.EntityType);

            if (!criteria.ArrayIsNullOrEmpty(criteria.ModifiedBy))
                bucket.PredicateExpression.AddWithAnd(AuditEntryFields.ModifiedBy == criteria.ModifiedBy);

            if (criteria.PrimaryKeys != null && criteria.PrimaryKeys.Length > 0)
            {
                IPredicateExpression peAggregate = new PredicateExpression();
                foreach (AuditCriteria.PrimaryKeyStruct pk in criteria.PrimaryKeys)
                {
                    IPredicateExpression pe = ((AuditEntryFields.PrimaryKeyName0 == pk.Name) & (AuditEntryFields.PrimaryKeyValue0 == pk.Value))
                        | ((AuditEntryFields.PrimaryKeyName1 == pk.Name) & (AuditEntryFields.PrimaryKeyValue1 == pk.Value))
                        | ((AuditEntryFields.PrimaryKeyName2 == pk.Name) & (AuditEntryFields.PrimaryKeyValue2 == pk.Value));
                    peAggregate.AddWithOr(pe);
                }
                bucket.PredicateExpression.AddWithAnd(peAggregate);
            }

            return bucket;
        }

        private void SetUpFetchRecent(string entityType, string userName, out RelationPredicateBucket bucket, out SortExpression sort)
        {
            AuditCriteria criteria = new AuditCriteria();
            criteria.EntityType = new string[] { entityType };
            criteria.ModifiedBy = new string[] { userName };
            criteria.SearchType = AuditCriteria.SearchTypeEnum.RecentlyModified;
            bucket = CreatePredicate(criteria);
            sort = new SortExpression(AuditEntryFields.DateModified | SortOperator.Descending);
        }
        #endregion
    }
}

Don't use sql_variant columns if you can avoid it. It's mapped on a System.Object type, and in general not usable. Either use Image or Text fields instead (or on sqlserver 2005: varbinary MAX)

Thanks Frans, I'll replace them. What field type would you recommend (Image vs ntext as I don't have sql 2k5) Is there any reason that you know of why the triggers I was looking at were not using text/image fields? I assumed there must be a brutal penalty to pay to give up the ability to audit large fields. If there is a high cost, could it be remediated by having two fields to save data in, a binary/ntext and an nvarchar? Thanks

Also a side question, is there a benefit to storing the after values beyond making it easier to identify what a particular person changed? It seems like only the before values are strictly necessary so long as you have access to the current state of the object in question.