In my DB (Postgres 9.4) I 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 I 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 I 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 I could change the elements' color atomically.
Thank in advance and sorry for my bad english.