代码之家 › 专栏 › 技术社区 › Ilia K

当通过NEW更改字段时,不会执行Postgres触发器

triggers postgresql

1

Ilia K · 技术社区 · 7 月前

在Postgres中,当通过BEFORE触发器中的NEW对象更改表中的字段时,如何执行触发器来更改此字段?

例子: 第一个触发器“trigger1”:

CREATE OR REPLACE FUNCTION trigger1()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    --...
    NEW.time_spent = NEW.etime - NEW.stime;
    NEW.cost := NEW.time_spent * NEW.rate; -- field "cost" has been changed!
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER before_100_calc_cost BEFORE INSERT OR UPDATE OF stime, etime ON table1 FOR EACH ROW EXECUTE PROCEDURE trigger1();

第二个触发器“trigger2”,由于某种原因未执行:

CREATE OR REPLACE FUNCTION trigger2()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger "trigger2" did not start!((';

    -- some actions...
END;
$BODY$;

CREATE TRIGGER after_104_calc_parent_cost AFTER INSERT OR UPDATE OF cost OR DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE trigger2();

表1:

CREATE TABLE IF NOT EXISTS table1 (
    id serial PRIMARY KEY,
    parent_id integer,
    stime timestamptz NOT NULL DEFAULT NOW(),
    etime timestamptz DEFAULT NULL,
    time_spent real DEFAULT NULL,
    rate numeric204 DEFAULT NULL,
    cost numeric204 DEFAULT NULL
);

测试用例:

INSERT INTO table1 (stime) VALUES (NOW() - INTERVAL '1 hour');
UPDATE table1 SET etime = NOW(); -- trigger "trigger2" did not start!

当执行“trigger1”并更改“成本”字段时,触发器“trigger2”不会触发!

1 回复 | 直到 7 月前

1

0

Zegarek 7 月前

如果你修复了数据类型,缺少 return 在第二触发器中, it'll work as expected .

初始 insert 同时触发两个触发器,然后更新只触发 trigger1 因为尽管它确实试图改变 cost ,它不在的目标列中 update 声明和 trigger2 定义为仅在满足以下条件时才开火( update or insert of cost ). 引用 CREATE TRIGGER doc :

For UPDATE 事件,可以使用以下语法指定列列表:
UPDATE OF column_name1 [, column_name2 ... ]
触发器仅在以下情况下才会触发 列出的列中至少有一列被提及为 更新 命令,或者如果列出的列之一是依赖于作为目标的列的生成列 更新 .

它也改变了它从一个 null 静止a 无效的 ,所以你不会通过比较来理解它 NEW vs OLD 要么。你走了 rate 如果没有默认值,并且您没有在插入、更新或触发器中填写它,那么它将使应该改变成本的表达式无效。

我猜你早就料到了 update of cost 每当有任何东西以任何方式接触到田地时,就会开火,并对其进行更新,但事实并非如此。如果修复了成本计算,则可以设置 触发器2 当事件最终通过使用 trigger..when(condition) :

condition
一个布尔表达式,用于确定是否实际执行触发器函数。如果 WHEN 如果指定了,则只有在条件返回true时才会调用该函数。In FOR EACH ROW 触发器 什么时候 条件可以通过以下方式引用旧行和/或新行值的列 OLD.column_name 或 NEW.column_name 分别。当然, INSERT 触发器不能引用 老 和 DELETE 触发器不能引用 新 .

_{demo at db<>fiddle}

CREATE TRIGGER after_104_calc_parent_cost 
  AFTER UPDATE ON table1 
  FOR EACH ROW 
  WHEN (NEW.cost is distinct from OLD.cost)
  EXECUTE PROCEDURE trigger2();