Automatic DB logging (modifiedby etc.)?

Aug 27, 2007 at 10:33 AM
Hi folks,

All my tables contain fields called “ModifiedBy” and “ModifiedOn” which logically exist as logging purpose.

What I would like to do is to automatically update those fields during an update or insert action. I can do it manually in my presenter for each entities but what’s a pain (and DRY violation)!

So I’m wondering what could be the best (and generic) way to do it using the Repository Factory? There is no event dispatched before save or add.

Any ideas?

Thanks,
Stéphane Bebrone.
Aug 27, 2007 at 4:51 PM
In general, it's expected that such "bookkeeping" fields would be updated by the stored procedure used to do the actual insert or update.
Aug 28, 2007 at 9:40 AM
Thanks for your answer.

Yes, I can do it at sp level but that means that I have to update my generated CRUD sp by hand and set the “user” parameter for each entities.

I’m pretty sure that it must exist a more efficient way.
For instance, Subsonic intercepts db calls and binds requested fields (based on a hardcoded convention I agree), it’s definitively the way I would like to implement.
http://tinyurl.com/ywouk3

OK, the repository isn’t aware about DB’s schema but we can use entity’s property. Isn’t it?

Greets,
Stéphane Bebrone.
Sep 13, 2007 at 6:22 PM
IMO, this is inherently the duty of the RDBMS. Since stored procedures are the interface between your code and the RDBMS, this logic belongs even lower than SPs.

I've always done this in triggers on the tables in question, and just made it a part of the table creation process.

It's fairly easy to create a SQL script that spits out triggers for all your tables if you have a lot of existing tables that don't have them, as well.
Sep 18, 2007 at 3:49 PM
Hi shaoken,

I have the database schema similar to your (RecStatusID, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy). I solve this my modified the TableHelper.t4 template. Basically, when the CRUD generate, it doesn't include these (RecStatusID, CreatedDate, CreatedBy, ModifiedDate) columns in the parameter. I only included ModifiedBy in the parameter list. The others ((RecStatusID, CreatedDate, CreatedBy, ModifiedDate) I used the default value in the update/insert statement.