Thread: How to prevent modifications in a tree of rows, based on a condition?
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;
Re: How to prevent modifications in a tree of rows, based on a condition?
From
"A. Kretschmer"
Date:
am Tue, dem 19.06.2007, um 12:23:51 +0200 mailte Philippe Lang folgendes: > > 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. > > > Does anyone have an idea maybe? The rule system? Thanks for your ideas. Yes, with RULEs. A simple example to prevent UPDATE for subset rows: test=# select * from foo; id | t ----+----- 5 | 1,2 6 | 2,2 7 | 3,2 1 | 1 1 | 1 0 | 10 0 | 10 (7 rows) test=*# select * from looked ; id ---- 1 (1 row) -- i want to prevent update for all id's listed in table looked test=*# create rule r1 as on update to foo where old.id in (select id from looked) do instead nothing; CREATE RULE test=*# update foo set t = 100 where id=0; UPDATE 2 test=*# update foo set t = 100 where id=1; UPDATE 0 test=*# select * from foo; id | t ----+----- 5 | 1,2 6 | 2,2 7 | 3,2 1 | 1 1 | 1 0 | 100 0 | 100 (7 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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