From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | How to prevent modifications in a tree of rows, based on a condition? |
Date: | 2007-06-19 10:23:51 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F4218CE95@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2007-06-19 11:18:03 | Re: How to prevent modifications in a tree of rows, based on a condition? |
Previous Message | Andrew Kelly | 2007-06-19 08:23:27 | Re: [GENERAL] [PERFORM] Postgres VS Oracle |