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?
