postgresql Postgres 插入或更新触发 WHEN 条件（旧）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36029446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres insert or update trigger WHEN condition (old)
提问by Artemiy StagnantIce Alexeew
I need write insert or update trigger, but with WHEN condition with compare OLD and NEW rows.
我需要写入插入或更新触发器，但使用 WHEN 条件比较 OLD 和 NEW 行。
According documentation OLD is null for insert operation. How i can use OLD in WHEN condition for INSERT AND UPDATE triggers?
根据文档，插入操作的 OLD 为空。我如何在插入和更新触发器的 WHEN 条件下使用 OLD？
Example trigger:
示例触发器：
CREATE TRIGGER mytrigger
BEFORE INSERT OR UPDATE ON "mytable"
FOR EACH ROW
WHEN (NEW.score > 0 AND OLD.score <> NEW.score)
EXECUTE PROCEDURE mytrigger();
but for insert OLD is null.
但对于插入 OLD 为空。
回答by Elad
Option A:
选项A：
You can change the code so that conditions will be in the trigger function rather than the trigger itself. With this approach OLDwill be used only in the UPDATE.
您可以更改代码，使条件位于触发器函数中，而不是触发器本身。这种方法OLD只会在UPDATE.
Trigger:
扳机：
CREATE TRIGGER mytrigger
BEFORE INSERT OR UPDATE ON "mytable"
FOR EACH ROW
EXECUTE PROCEDURE mytrigger();
Trigger function:
触发功能：
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
Option B:
选项 B：
As Thilo suggested write two triggers that share the same trigger function.
正如 Thilo 建议的那样，编写两个共享相同触发器功能的触发器。
Triggers:
触发器：
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();
Trigger function:
触发功能：
CREATE OR REPLACE FUNCTION mytrigger()
RETURNS trigger AS
$BODY$
begin
YOUR CODE
return new;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
回答by vembutech
Trigger.oldMap.keySet(); will give the Id's present in the Trigger.oldMap. It is a set type collection of all the Id's. have a look at the following example, change the DML events in the trigger events every time and see the debug logs. you will understand which trigger context variable is available for which DML event.
触发器.oldMap.keySet(); 将在 Trigger.oldMap 中给出 ID。它是所有 Id 的集合类型集合。看看下面的例子，每次都改变触发器事件中的DML事件并查看调试日志。您将了解哪个触发器上下文变量可用于哪个 DML 事件。
CREATE TRIGGER Email_Check_On_Contact before update{ oldMap=new Map(); o = trigger.oldMap; for(Contact newcont: trigger.new) { if(newcont.Email != o.get(newcont.Id).Email) { newcont.Email.addError('Email cannot be changed'); } } }
更新前创建触发器 Email_Check_On_Contact{ oldMap=new Map(); o = 触发器.oldMap; for(Contact newcont: trigger.new) { if(newcont.Email != o.get(newcont.Id).Email) { newcont.Email.addError('Email 不能更改'); } } }
