postgresql - 触发，更新字段更新时间戳

Question

背景:我们有一个 completed_flag，默认为 0，当有人完成调查时更新为 1。我想记录这次更新发生的时间戳

在编写了这个触发器/函数以在标志从 0 触发到 1 时更新时间戳后，我怀疑我这样做是否是最好的方法。我怀疑这不是最好的方法，但我对 postgres 有点陌生。触发器能否仅在该值发生变化时调用该函数？这样就不需要在每次 ROW 更新时检查这个值，这看起来太过分了

-- add new column to store time the respondent completed
ALTER TABLE  tbl_pXXXX ADD COLUMN complete_time timestamp without time zone;


-- function to insert timestamp
CREATE FUNCTION completed_timestamp() RETURNS trigger AS $$
   LANGUAGE plpgsql
BEGIN
 IF new.complete_flag = 1 and old.completed_flag = 0
            THEN UPDATE tbl_pXXXX
            SET complete_time = current_timestamp
END;
$$;

-- trigger to call the function
CREATE TRIGGER update_timestamp
AFTER UPDATE ON tbl_pXXXX
FOR EACH ROW 
EXECUTE PROCEDURE completed_timestamp();

Answer 1

只要您只对触发触发器的行进行更改，使用 BEFORE UPDATE 就简单得多(也更便宜)触发器，可以更改 new.complete_flag在将其保存到表中之前，不必运行 UPDATE在它已经在那里之后的声明。

您还可以使用 UPDATE OF <column> 将触发器限制为更新特定字段 CREATE TRIGGER 的条款声明。

最终结果看起来像这样:

CREATE FUNCTION completed_timestamp() RETURNS trigger AS $$
   LANGUAGE plpgsql
BEGIN
  IF new.complete_flag = 1 and old.completed_flag = 0 THEN
    new.complete_time := current_timestamp;
  END IF;
  RETURN new;
END;
$$;

CREATE TRIGGER update_timestamp
BEFORE UPDATE OF completed_flag ON tbl_pXXXX
FOR EACH ROW 
EXECUTE PROCEDURE completed_timestamp();