Philippe Lang wrote:
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.
We do this with BEFORE triggers. If the "flag_closed" column = 'Y', the
modification is prevented.
In the future we are also going to put in an exception for some
privileged group, such as the "purge" group perhaps, that will allow
that group to still make deletes, but only deletes.
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 majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com www.andromeda-project.org
631-689-7200 Fax: 631-689-0527
cell: 631-379-0010
