Postgres插入或更新条件时的触发器（旧） [英] Postgres insert or update trigger WHEN condition (old)

问题描述

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

I need write insert or update trigger, but with WHEN condition with compare OLD and NEW rows.

根据文档说明，插入操作的OLD为空。

According documentation OLD is null for insert operation. How i can use OLD in WHEN condition for INSERT AND UPDATE triggers?

示例触发器：

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为空。

but for insert OLD is null.

推荐答案

选项A：

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

You can change the code so that conditions will be in the trigger function rather than the trigger itself. With this approach OLD will be used only in the 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建议编写两个具有相同触发功能的触发器。

As Thilo suggested write two triggers that share the same trigger function.

触发器：

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

这篇关于Postgres插入或更新条件时的触发器（旧）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！