容器內的元素：如何對它們執行原子操作？

在我的數據庫（Postgres 9.4）中，我有以下物品：T 卹、牛仔褲、鞋子和盒子。

1. T卹、牛仔褲、鞋子可以放在一個盒子裡，它們可以有顏色（顏色可能為空）。
2. 盒子的每個元素必須具有相同的顏色。

為了檢查（並確保）我使用觸發器的最後一個條件。

/*
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;

現在，假設我想將 Box 內任何項目的顏色從“藍色”更改為“紅色”（假設 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;

由於觸發器，此程式碼將失敗。

我可以更改框中每個項目的顏色的唯一方法是：

1. 從框中刪除所有項目（將其 box_id 設置為 null）。
2. 更改所有元素的顏色。
3. 將所有元素放入盒子中（設置它們的 box_id）。

有沒有辦法（通過建模或觸發器）來避免這種棘手的情況？如果我能原子地改變元素的顏色，那就太好了。

提前感謝並為我的英語不好感到抱歉。

我建議調整您的模型並完全取消觸發器。雖然這個例子有點令人費解，但您的數據模型包含一個重複的依賴項，它是您更新異常問題的根源。

T 卹 (T)、牛仔褲 (J)和鞋子 (S)可以放在盒子 (B)中，它們可以有顏色 (C)。

盒子 ( B )的每個元素 ( E )必須具有彼此相同的顏色 ( C )。

好的，所以 T → B，T → C，B → C。也就是說，如果我們知道 T 卹，我們就知道 Box，如果我們知道 T 卹，我們就知道 Color，如果我們知道 Box ，我們知道顏色。由於 Color 依賴於 Box 並且 Box 依賴於 T-shirt，雖然可能不是特別直覺，但 Color 不能直接依賴於 T-shirt，因為它已經通過傳遞依賴關係依賴。也就是說，如果我們知道 T 卹，我們就無法知道顏色（顏色是NULL），因為我們必須先知道 Box。

現有觸發器實際上是在嘗試補償 3NF+ 數據模型可以緩解的潛在更新異常。如果在創建時知道 T 恤的顏色，則可以（並且可能應該）立即將這樣的 T 卹添加到相同顏色的盒子中。這樣，只需要更新盒子本身的顏色屬性就可以改變盒子中所有物品的顏色。

此外，除非有充分的理由將 T 卹、牛仔褲和鞋子拆分到自己的表中，否則最好將它們合併到具有 ElementType 查找屬性的 Element 表中，但很像@dezso，我懷疑這是甚至不是關於 T 卹、牛仔褲和鞋子的顏色，因為它們被安排在不同的盒子裡。

讓觸發器考慮一組相關更改而不是獨立更新的具體問題可以通過以下方式解決：

• 將觸發器聲明為在送出時延遲的約束觸發器。

CREATE CONSTRAINT TRIGGER trigger_name
 AFTER INSERT OR UPDATE ON table_name
 DEFERRABLE INITIALLY DEFERRED
 FOR EACH ROW EXECUTE PROCEDURE check_colors();

• 並將一組更改包含在事務中（BEGIN/COMMIT 對）

BEGIN;
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;
COMMIT;

儘管如此，並發事務應該被程序阻止以在相同的“盒子”上工作，否則基於觸發器的檢查可能會讓不連貫性通過。這是因為觸發器在執行時看不到其他尚未送出的會話的潛在更改。

引用自：https://dba.stackexchange.com/questions/118265