I am attempting to accomplish a soft delete with a system using
PostgreSQL, Hibernate, and EJB3(JBoss). I have set up tables with a
column to indicate deleted state. What is the best way to accomplish
this? I have looked into triggers and rules but end up with the same
problem. How can I return to Hibernate that 1 row was effected by the
SQL delete. With a trigger, you must return NULL if you do not want the
action to continue. With a rule, it works fine but the number of rows
effected that is returned is always 0. I has something to do with
command status but I don't can't find an example that works.
My current rule is
CREATE RULE XXXXXX AS ON DELETE TO XXXX_VIEW
DO INSTEAD UPDATE XXXXXX SET DELETED = 1 WHERE OLD.ID = ID;
When you execute a SQL delete, the row is flagged with DELETED=1 but
the returned row count = 0 and Hibernate throws and exception.
Does anyne have a work around for this problem?
