I have two tables in my db: order and deliveries. When creating order, I generate at the same time a number of deliveries depending on the order's start and end dates.
Deliveries have boolean done field, that is checked when the delivery is done. I have to suppress deleting any order if it has any deliveries that are done -otherwise the deliverer will loose the data (f.ex. delivery weight), as such info is stored in the order. Only in the case that none of deliveries is done, I can delete them together with the order.
In such case I can use rules (Disable DELETE on table in PostgreSQL?), but it seems not to work in my case: "syntax error close to IF". Are rules good here? Why can't I get my IF-THEN statement working?
CREATE RULE delete_order AS ON DELETE TO orders DO INSTEAD
(
DELETE FROM deliveries WHERE (order = OLD.id AND done = false);
IF (NOT EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id)) THEN
DELETE FROM orders WHERE id = OLD.id;
ELSE
RAISE NOTICE 'Cannot delete order that is partially done.';
END IF;
)
You are better off writing a trigger function here.
CREATE FUNCTION delete_order () RETURNS trigger AS $$
BEGIN
IF EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id AND done = TRUE) THEN
RAISE NOTICE 'Cannot delete order that is partially or completely done.';
ELSE
RETURN OLD;
END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER before_delete_order
BEFORE DELETE ON order
FOR EACH ROW EXECUTE PROCEDURE delete_order();
Every time you want to delete an order, the function delete_order() is called before the actual deletion takes place. If there are any deliveries already done then an error is raised, aborting the deletion. Otherwise OLD is returned and the system performs the deletion. This assumes that the deletion of the order record cascades to the associated deliveries; if not, the trigger function should probably delete those elivery records first in order not to get an FK violation when the order record is deleted.