PostgreSQL插入或更新触发器中的WHEN条件（旧值）

Question

我需要编写插入或更新触发器，但是在 WHEN 条件中要比较 OLD 和 NEW 行。

根据文档，对于插入操作，OLD 为 null。那么我如何在 INSERT 和 UPDATE 触发器的 WHEN 条件中使用 OLD 呢？

触发器示例：

CREATE TRIGGER mytrigger
    BEFORE INSERT OR UPDATE ON "mytable"
    FOR EACH ROW 
    WHEN (NEW.score > 0 AND OLD.score <> NEW.score)
    EXECUTE PROCEDURE mytrigger();

但是对于插入的 OLD 值为空。

- Artemiy StagnantIce Alexeew

---

你可以展示一下你的代码吗？ - Lukasz Szozda

OLD.score IS NULL 可能会有用。或者您可以拥有两个单独的触发器（仍然可以共享相同的过程）。 - Thilo

但根据文档，Postgres不能保证执行操作的顺序。例如，OLD.score可能会在OLD为null之前被执行。 - Artemiy StagnantIce Alexeew

INSERT触发器的WHEN条件不能引用旧值 第34行：... EW.score不为空且NEW.score <>''且（OLD.score。我需要分成两个触发器。 - Artemiy StagnantIce Alexeew

尝试在mytrigger()内部定义when condition。 - Shubham Batra

Answer 1

选项 A：

您可以更改代码，使条件将在触发函数中而不是触发器本身中。使用此方法，OLD 仅在 UPDATE 中使用。

触发器：

CREATE TRIGGER mytrigger
    BEFORE INSERT OR UPDATE ON "mytable"
    FOR EACH ROW 
    EXECUTE PROCEDURE mytrigger();

触发函数：

CREATE OR REPLACE FUNCTION mytrigger()
  RETURNS trigger AS
$BODY$
begin
if NEW.score > 0 then
     --code for Insert
     if  (TG_OP = 'INSERT') then
           YOUR CODE
     end if;

     --code for update
     if  (TG_OP = 'UPDATE') then
           if OLD.score <> NEW.score then  -- (if score can be null see @voytech comment to this post)
              YOUR CODE
           end if;
     end if;
end if;
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

选项 B：

按照Thilo的建议编写两个共享相同触发函数的触发器。

触发器：

CREATE TRIGGER mytrigger1
    BEFORE INSERT ON "mytable"
    FOR EACH ROW 
    WHEN NEW.score > 0
    EXECUTE PROCEDURE mytrigger();


CREATE TRIGGER mytrigger2
    BEFORE UPDATE ON "mytable"
    FOR EACH ROW 
    WHEN (NEW.score > 0 AND OLD.score <> NEW.score)
    EXECUTE PROCEDURE mytrigger();

触发函数：

CREATE OR REPLACE FUNCTION mytrigger()
  RETURNS trigger AS
$BODY$
begin
      YOUR CODE
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

- Elad

---

请记住，NULL不等于NULL - 因此，在比较OLD.score和NEW.score时，更明智的做法是使用IS DISTINCT FROM语句： OLD.score IS DISTINCT FROM NEW.score - percy

@voytech 我只是复制了他的条件。我们不知道 score 是什么类型。它也可能是一个 not null 字段。我们只能使用提供的信息进行工作 :) 无论如何，我已根据您的评论编辑了代码。 - Elad

但我想使用触发器外部的方法来进行优化。 - Artemiy StagnantIce Alexeew

@Artemiy StagnantIce Alexeew 看看选项B。 - Elad

Answer 2

Trigger.oldMap.keySet();会返回Trigger.oldMap中存在的Id。 它是所有Id的集合类型。 看一下以下示例，每次更改触发器事件中的DML事件并查看调试日志。 您将了解哪个触发器上下文变量适用于哪个DML事件。

    CREATE TRIGGER Email_Check_On_Contact
                 before update{
     oldMap<ID,Contact>=new Map<ID,Contact>();
     o = trigger.oldMap;
     for(Contact newcont: trigger.new)
     {
        if(newcont.Email != o.get(newcont.Id).Email)
        {
            newcont.Email.addError('Email cannot be changed');
        }
      }
   }

- vembutech

---

这似乎不是SQL或PL/pgSQL。 - pink_demon