How to prevent modifications in a tree of rows, based on a condition? - Mailing list pgsql-general

Hi,

I have a database which can be simplified in the following way, with
three tables:

An "order" has multiple "order lines", and an "order line" has multiple
"line steps".

I'd like to prevent any kind of modification (insert, update, delete) in
a order (and its lines, and steps) if all the steps in the lines of the
order are "checked". If that condition is not true, a modification is
accepted.

A brute force solution is to write 3 x 3 triggers, and code that
properly, but I suspect there something more elegant to do with
Postgresql?

Does anyone have an idea maybe? The rule system? Thanks for your ideas.

Philippe Lang

---------------

CREATE TABLE public.orders
(
  id         int4 PRIMARY KEY,
  value        int4
) WITHOUT OIDS;

CREATE TABLE public.order_lines
(
  id         int4 PRIMARY KEY,
  value        int4,
  order_id     int4 REFERENCES public.orders
) WITHOUT OIDS;

CREATE TABLE public.line_steps
(
  id             int4 PRIMARY KEY,
  value            int4,
  checked        bool,
  order_line_id     int4 REFERENCES public.order_lines
) WITHOUT OIDS;

-- Insert values
INSERT INTO orders VALUES (1, 1);

INSERT INTO order_lines VALUES (1, 1, 1);

INSERT INTO line_steps VALUES (1, 1, true, 1);
INSERT INTO line_steps VALUES (2, 2, true, 1);
INSERT INTO line_steps VALUES (3, 3, true, 1);

INSERT INTO order_lines VALUES (2, 2, 1);

INSERT INTO line_steps VALUES (4, 1, true, 2);
INSERT INTO line_steps VALUES (5, 2, true, 2);
INSERT INTO line_steps VALUES (6, 3, true, 2);

INSERT INTO order_lines VALUES (3, 3, 1);

INSERT INTO line_steps VALUES (7, 1, true, 3);
INSERT INTO line_steps VALUES (8, 2, true, 3);
INSERT INTO line_steps VALUES (9, 3, true, 3);

-- Modifications that should be forbidden
UPDATE orders SET value = 123 WHERE id = 1;
UPDATE order_lines SET value = 123 WHERE id = 1;
UPDATE line_steps SET value = 123 WHERE id = 1;

-- We show final data
SELECT
o.id AS order_id,
o.value AS order_value,
ol.id AS order_line_id,
ol.value AS order_line_value,
ls.id AS line_steps_id,
ls.value AS line_step_value,
ls.checked AS check

FROM orders AS o

INNER JOIN order_lines AS ol
ON o.id = ol.order_id

INNER JOIN line_steps AS ls
ON ol.id = ls.order_line_id

ORDER BY o.id, ol.id, ls.id;