Please help

I need to do audits for all data that is inserted, deleted, or updated in the database. My audit record should include a primary key value of the modified record. I have a problem with getting a primary key value for inserts:

My LLBLGen entity has a primary key fields specified as Identiy/Sequence fields, so I don't need to specify the primary key value for new entities - LLBLGen populates them for me when I call the SaveEntity method. But in order to create an audit record, I need to have the primary key value prior to executing SaveEntity.

Is there a way to get the next sequence value by using LLBLGen? Can I use the same method as LLBLGen uses to set the Identity/Sequence fields?

I have no answer for you.

But, I feel your pain.

This would be a good example for a section in llblgen to showcase architectural and on-the-fly type solutions to comon problems.

I think this would be a huge benifit and a significant selling point

What do you think?

The Identity values are handed out by the database. So it's impossible to get these prior to an insert, as they're generated at the insert. This is for a reason though: only in the INSERT T-SQL statement transaction the db knows exactly what the next value will be. Any other mechanism will fail in a multi-user environment.

I'm sorry, but if you need the value prior to the save, there is no other solution than to use a different kind of PK value (for example a GUID) or to log the insert after it has taken place (which is IMHO a better mechanism, as you then can be sure the save has been done).

Fishy wrote:

This would be a good example for a section in llblgen to showcase architectural and on-the-fly type solutions to comon problems.

The problem is common to all external code which is inserting data into tables with identity column pk's: you only know the value after the insert has taken place. You can't determine it up front, all code which tries that is not multi-user proof.

If you want to trigger code after a save, subscribe to an entity's AfterSave event. This event is called when a save succeeded, IMHO the right spot to log a successful save.