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;
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
