觸發器適用於 UPDATE，但適用於 INSERT

在我的數據庫模式中，一個組織可以有多個地址，但只有一個預設地址。我正在嘗試創建一個觸發器，如果在is_default插入或更新時將列設置為 true，它將其餘行設置為 false，將目前行設置為 true。

更新部分正在工作，但是我收到了插入的唯一約束錯誤。

這就是我所擁有的：

CREATE OR REPLACE FUNCTION public.ensure_only_one_default_address()
  RETURNS TRIGGER
 AS $$
 BEGIN
 -- nothing to do if updating the row currently enabled
 IF (TG_OP = 'UPDATE' AND OLD.is_default = true) THEN
     RETURN NEW;
 END IF;

 -- disable the currently enabled row
 EXECUTE format('UPDATE %I.%I SET is_default = false WHERE is_default = true AND organization_id = %L;', TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD.organization_id);

 -- enable new row
 NEW.is_default := true;


    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;


CREATE TRIGGER ensure_only_one_default_address
     BEFORE INSERT OR UPDATE OF is_default ON public.company_addresses
     FOR EACH ROW WHEN (NEW.is_default = true)
     EXECUTE PROCEDURE public.ensure_only_one_default_address();

發生約束錯誤是因為我有一個唯一的部分索引 on (organization_id, is_default) WHERE is_default = true，但我認為觸發器會首先觸發，然後是唯一約束，所以我想知道 Postgres 的內部是否有我遺漏的東西。

回答問題

我雖然觸發器會首先觸發，然後是唯一約束……

你想對了。但主要的錯誤是而不是在- 的情況下，它必然什麼都不做， where沒有定義。OLD.organization_id``NEW.organization_id``UPDATE``INSERT``OLD

這曾經在舊的 Postgres 版本中立即引發和異常。Postgres 11 的發行說明：

在 PL/pgSQL 觸發器函式中，OLDandNEW變數現在在未分配時讀取為 NULL (Tom Lane)

以前，對這些變數的引用可以被解析但不能被執行。

去顯示聲明 Postgres 版本正在使用的重要性……

改用：

CREATE OR REPLACE FUNCTION public.ensure_only_one_default_address()
 RETURNS TRIGGER
 LANGUAGE plpgsql AS
$func$
BEGIN
  -- nothing to do if updating the row currently enabled
  IF TG_OP = 'UPDATE' THEN
     IF OLD.is_default THEN                      -- !!!
        RETURN NEW;
      END IF;
  END IF;

  UPDATE public.company_addresses
  SET    is_default = false
  WHERE  is_default
  AND    organization_id = NEW.organization_id;  -- !!! NEW, not OLD

  RETURN NEW;
END
$func$;

CREATE TRIGGER ensure_only_one_default_address
BEFORE INSERT OR UPDATE OF is_default ON public.company_addresses
FOR EACH ROW WHEN (NEW.is_default)
EXECUTE PROCEDURE public.ensure_only_one_default_address();

我做了更多：

在外層OLD的案例中引用是不安全的。INSERT（Postgres 可以自由地以任意順序評估表達式。）我將其隱藏在嵌套IF語句中。

INSERT通常，只為and編寫單獨的觸發器函式和触發器會更簡潔UPDATE（除非這會導致大量程式碼重複）。

對於動態 SQL，將值 OLD.organization_id作為值傳遞。但為什麼要首先使用動態 SQL 呢？我把它變成了靜態的。更乾淨，更快。

is_default = true只是一種吵鬧的說法is_default。

替代 CTE 且無觸發器

WITH pre_emptive AS (
  UPDATE company_addresses
  SET    is_default = false
  WHERE  is_default
  AND    organization_id = 2
  )
INSERT INTO company_addresses(organization_id, is_default) VALUES (2, true);

也就是說，我建議採用完全不同的方法：

替代數據庫設計

更新其他行以將新行標記為“預設”是低效的。

考慮為每個持有（並指向）“預設”的組織添加 am email 列：

CREATE TABLE company_addresses (
 organization_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, organization    text UNIQUE NOT NULL
, email           text UNIQUE -- FK added below  -- can also be NOT NULL
);

CREATE TABLE email (
 email           text PRIMARY KEY
, organization_id int NOT NULL REFERENCES company_addresses ON DELETE CASCADE
, UNIQUE (organization_id, email)  -- seems redundant, but required for FK
);

ALTER TABLE company_addresses
 ADD CONSTRAINT company_addressesn_default_email_fkey
 FOREIGN KEY (organization_id, email) REFERENCES email (organization_id, email);

那麼你根本不需要觸發器。還有其他各種優勢——比如你在主表中有預設的電子郵件，沒有加入。

詳細評估：

• 跨表唯一

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