Hi

I'm working on a database to be used for researche purposes. It is important for me to keep track of all changes of the database. This means that I dont want to delete records, just mark them "deleted".

Is there a proposed way to do this,,, best practice, or can anyone share their experience about implementing such a behaviour?

//regards
//lasse

Hi Lasse,

the first thing which springs in my mind is to add a deleted flag to each of your database tables. To delete a record you just have to set this flag to "true". Each query has to regard the delete flag to ensure that only valid records are retrieved. I'm afraid that NHibernate does not support to automate this behavior. To set the delete flag you may write a class which uses NHibernate's IInterceptor interface (see http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/manipulatingdata.html#manipulatingdata-interceptors). The OnDelete() method is called just before deleting a record in the database. Don't forget to remove the "deleted" object from the session cache using Session.Evict() after setting the flag and updating the record ;-)

Regards
Klaus

I am pretty new to hibernate/nhibernate but shouldnt this work? at least it works for me, and does not look that much as a hack.

I implemented this in SQL Server 2005 using INSTEAD OF triggers on delete for any soft delete table. The triggers set the delete flag and perform clean-up. The beauty of this solution is that it correctly handles deletes issued by any system that accesses the database. INSTEAD OF is relatively new in SQL Server, I know there's an Oracle equivalent.

This solution also plays nicely with our O/R mapper -- I created views that filter out soft deleted records and mapped those. The views are also used for all reporting.

ref: http://stackoverflow.com/questions/133143/llblgen-how-can-i-softdelete-a-entry#133609

any idea how to handle unique keys in combination with an delete_flag? i.E. you have a
PK = ID
UK = NAME

now you logical delete a record, by setting the deletedate. After this, a new record with the same name comes into the system. I.e hibernate would now try to create a new record with a new PK but the same UK. Which causes the UK to fire an exception.

It depends on the purpose of the unique constraint (UC). If the UC is on field(s) that compose an alternate primary key, then you may want to recover (undelete) the existing record instead of inserting a new one.

If that's not the case, then I can think of two options:

1) Modify the logical delete trigger I wrote about in a previous reply to delete the record and insert it into a deleted records table.
2) Use a trigger to enforce a UC on active records only.

i made the secound suggestion to our db team already, but they want me to change my program, so that no triggers have to be written :/ thats why i asked here in the forum. I thought maybe something like: while searching for a new id, check the table for an already existing record, take its id and overwrite the record later. I hoped that this would be something that already somebody else had done. But it doesn't seam so. At least when I read the articles in the web :/

Thanks anyway. I will try to do a secound request. THX

I should have thought of this last night when I replied:

3) Add the DeleteFlag into the unique constraint so that the constraint is on the combination of Name, DeleteFlag. The only problem with this is that you would only be able to have one deleted record for a Name. You could get around this problem by using a DateDeleted field in the constraint instead of DeleteFlag.

we came to the same conclusion while I was talking to him. Hearing the same from you, means that we are on the right track. Thank you again!

what a nice solution for logical deletion, thanks for sharing.

one (hibernate) issue i've found is on mapping a "one to many" relation as set property.

where


in such a case "logically deleted" records are (erroneously) included in set.

in order for this to work as expected (logically deleted records not being included), i had to add again the "where not deleted" attribute as in the following fragment:




thanks again for sharing this idea,
-fb