inIn my DB (Postgres 9.4) iI have the following items: Tshirts, Jeans, Shoes and Boxes.
- Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
- Each element of the box MUST have the same color of the other.
To check (and also to ensure) the last condition iI use a trigger.
/*
If I try to INSERT (OR UPDATE) an item in a box
containing elements of different color, it raises an exception.
*/
BEGIN
IF (
SELECT color FROM tshirt WHERE tshirt.box_id = NEW.box_id
UNION SELECT color FROM jeans WHERE jeans.box_id = NEW.box_id
UNION SELECT color FROM shoes WHERE shoes.box_id = NEW.box_id
) <> NEW.color THEN
RAISE EXCEPTION 'Error..';
RETURN NULL;
END IF;
END;
Now, let's suppose iI want to change the color from 'Blue' to 'Red' for any item inside a Box (let's say box_id = 1):
UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
UPDATE Shoes SET color = 'Red' WHERE box_id = 1;
This code will fail because of the trigger.
The only way I can change the color of each item in the box is:
- Remove all items from the box (setting their box_id to null).
- Change the color of all the elements.
- Put all the elements inside the box (setting back their box_id).
Is there any way (through modeling or triggers) to avoid this tricky situation? It would be great if iI could change the elements' color atomically.
Thank in advance and sorry for my bad english.
