I'm trying to implement a logical delete for one of my objects managed by Hibernate. For a deletion of my object type "Item", I want to set isActive=false and modifiedDate=currentDate, and not delete the record from the db. I also want any associated objects to be deleted (true delete, not logical) as setup by the cascade parameters on the associations. There is also a set of associated child "Item" objects, which should be processed by the same logical delete algorithm I just described. Essentially a delete of an "Item" can result in a deletion of a tree of Items.

From reading other posts on this topic I found I could specify custom sql with sql-delete. This works great. The entire tree is deleted exactly as described above, but the only problem is that it is nearly impossible to write sql to do what I want that will work on multiple databases.

The only other solution I found was to implement a delete event listener of some type, but everything I read left me thinking that the cascading would be difficult to implement (especially when we are talking about deleting a tree of items). Can this be done? The advantage of this is that I wouldn't have to write db specific sql.

how about using

org.hibernate.Interceptor.onPrepareStatement(String sql)

and change the delete statement to an update statement?