Hey Frans,

I think we have this worked out, but I just wanted to run it by you before we started coding it, to see if you see any flaws in using this method.

We currently override SaveEntity to save four generic fields (we call them "audit fields")that we have on every single table our database. This works beautifully, and allows us to avoid the maintenance of a trigger for each table.

In addition to the audit fields, we are going to add an audit function to a few (<10) tables, for which we need complete audit trails. Basically, we will have an exact copy of each of these tables, with a few additional fields (a unique ID and an action--create/update/delete). So the "audit table" will have the same number of fields as the "base table" plus two.

We have decided to override LLBL SaveEntity and DeleteEntity in lieu of writing triggers. Any time an Entity that correlates to one of these base tables is saved or deleted, it will be "intercepted" in these methods and an audit table Entity will immediately be created and saved.

The values for the audit Entity fields can be generated in a very generic way, because it is a requirement that the tables' fields ALWAYS be kept in synch.

The audit table will have NO formal relationship to the base table for a number of reasons, one of which is to avoid FK constraint errors on deletes (we could actually get around this if we needed to). There are other reasons why it makes more sense to have this code sit at the lower (adapter) level for our project.

Hopefully the above makes sense, at least from a readability perspective.

My main question is whether calling SaveEntity from within SaveEntity or DeleteEntity will cause any problems.

Also, if I can make these saves work, I assume the "audit save" will occur before the Save/Update/Delete of the base table Entity? Will it participate in the current transaction?

We would have to re-write this code (which will likely only amount to about 30 lines in C#) if we switched to Oracle?

Again, I hope the above makes sense. Any thoughts would be deeply appreciated!

psandler wrote:

Hey Frans,

I think we have this worked out, but I just wanted to run it by you before we started coding it, to see if you see any flaws in using this method.

We currently override SaveEntity to save four generic fields (we call them "audit fields")that we have on every single table our database. This works beautifully, and allows us to avoid the maintenance of a trigger for each table.

In addition to the audit fields, we are going to add an audit function to a few (<10) tables, for which we need complete audit trails. Basically, we will have an exact copy of each of these tables, with a few additional fields (a unique ID and an action--create/update/delete). So the "audit table" will have the same number of fields as the "base table" plus two.

We have decided to override LLBL SaveEntity and DeleteEntity in lieu of writing triggers. Any time an Entity that correlates to one of these base tables is saved or deleted, it will be "intercepted" in these methods and an audit table Entity will immediately be created and saved.

The values for the audit Entity fields can be generated in a very generic way, because it is a requirement that the tables' fields ALWAYS be kept in synch.

The audit table will have NO formal relationship to the base table for a number of reasons, one of which is to avoid FK constraint errors on deletes (we could actually get around this if we needed to). There are other reasons why it makes more sense to have this code sit at the lower (adapter) level for our project.

Hopefully the above makes sense, at least from a readability perspective.

My main question is whether calling SaveEntity from within SaveEntity or DeleteEntity will cause any problems.

No, unless you pass in the same entity It then will of course loop.

Also, if I can make these saves work, I assume the "audit save" will occur before the Save/Update/Delete of the base table Entity? Will it participate in the current transaction?

Every call to a save method on the same adapter uses the transaction the adapter is in so yes.

If you override SaveEntity(), any call from the base' method to SaveEntity (like in a recursive save) will end up in your derived class first. Your code should anticipate on that.

We would have to re-write this code (which will likely only amount to about 30 lines in C#) if we switched to Oracle?

You can of course re-use the derived class for the Oracle DataAccessAdapter Code is the same.

Hi, I've got a follow-on question here. I'm finally getting around to putting in some auditing in my next database, and I'm trying to override the Adapter object to add the appropriate audit data. I have some sequence/identity columns though, and as a result, I'm having trouble getting the primary key of a new entity into my audit table since it isn't known until the transaction completes. Is there a recommended way to handle this? The only thing I can think to do is override all of the adapter's relevant On___ methods and create a collection of AuditEntity objects with object references to each object that is saved. Then once the transaction completes, I go through each AuditEntity and get the primary key value. Finally, I'd then save all of the AuditEntity items.

This doesn't strike me as efficient, clean, or guaranteed to work. :-) I'm hoping there's another way. If not, I'll probably just go w/ GUID's instead and bypass the headache. However, I'd like to know if there's an elegant solution out there that I'm missing.

Thanks all.

jd wrote:

Hi, I've got a follow-on question here. I'm finally getting around to putting in some auditing in my next database, and I'm trying to override the Adapter object to add the appropriate audit data. I have some sequence/identity columns though, and as a result, I'm having trouble getting the primary key of a new entity into my audit table since it isn't known until the transaction completes.

No, it's known when the entity is saved, during the transaction.

Is there a recommended way to handle this? The only thing I can think to do is override all of the adapter's relevant On___ methods and create a collection of AuditEntity objects with object references to each object that is saved. Then once the transaction completes, I go through each AuditEntity and get the primary key value. Finally, I'd then save all of the AuditEntity items.

I'm not sure how your audit entity is setup (general 'ID' field which can be anything, or per entity type an audit entity type), but what you could do is: in the OnBeforeEntitySave method override (1.0.2005.1) of DataAccessAdapter, subscribe to the entity's AfterSave event. In the handler there, add the new audit entity to a unitofwork object you're keeping around for this process. Then unsubscribe to the event.

After the save routine returns, you commit the unitofwork object in the same transaction, by passing in the same dataaccess adapter to the Commit() routine of the unitofwork.

This requires that you manually start the transaction first, then do the save actions, then commit the extra unitofwork, then commit the transaction.

IMHO that will always work.

Perfect. As usual that's precisely what I needed. Thanks.