In most of our tables, we maintain CreateDate and ModifyDate fields to provide minimal auditing of row changes. Because we cannot trust that the machine executing the query has the correct date/time, we rely on the database server for current date/time.

I am seeking a best practice on how to update these field values on the save and update of an entity. I have come up with the following options thus far. During SaveOrUpdate :

1) Somehow intercept the INSERT or UPDATE query statement and set CreateDate/ModifyDate = current_time(). This is optimal since it would require just 1 database call.

2) Make one database call to retrieve current_time(). Then in an interceptor, find CreateDate/ModifyDate parameters if they exist and update the datetime value. Make a 2nd database call to save or update.

3) Use database triggers to update CreateDate and ModifyDate. I'm not a huge fan of going with triggers, since it requires more database management to ensure that they are properly configured on every table.

Is option 1) even possible? Or is there a better way altogether?