4

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;
)
Improve this question
2
  • 1
    The question needs table definitions for involved tables (\d tbl in psql) and the version of Postgres. Commented May 22, 2014 at 23:24
  • Use ON DELETE RESTRICT in your foreign key definition. Commented May 23, 2014 at 5:54

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for this explanation. So, if I RAISE a notice in my BEFORE DELETE trigger, the delete action will be cancelled, and no row affected in the order table? Am I right?
That's correct. See the documentation here. If a DELETE trigger does not RETURN OLD then the operation is cancelled. In the trigger function you never do the operation yourself, the system will do that, but you can modify the process (i.e. insert other data, do an operation on another table) or even cancel it.
Patrick, your solution works. I just had to change RAISE NOTICE to RAISE EXCEPTION. Thanks!
2

You can do it using Postgres Rule without triggers

create function fn_prevent_deleting(OLD RECORD, tableName text)
returns void as
$BODY$
begin
    //Some condition here
   if (OLD.employee_id is not null) then
     raise exception 'Cant delete row on table %', tableName;
   end if;
end;
$BODY$
LANGUAGE plpgsql volatile;

CREATE RULE delete_order AS ON DELETE TO orders DO INSTEAD
select fn_prevent_deleting(OLD, 'orders');
Improve this answer

2 Comments

Thanks! And where does the part responsible for deleting and checking copndition go? Shall I put IF EXISTS(SELECT 1 FROM deliveries WHERE order = OLD.id AND done = TRUE) before raising exception and do the delete operation in else clause?
I've added condition to a pgsql function as example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.