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

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@postgresql.org 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