postgresql - 触发器，更新字段更新的时间戳 [英] postgresql - trigger, update timestamp on field update

问题描述

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

当这个触发器/函数在从0到1触发一个标志时更新时间戳之后，我在质疑我是否这是最好的方法。我怀疑这不是做这件事的最好方法，但我对postgres有点新鲜。只有在该值发生变化时，触发器才能调用该函数吗？这将消除每个ROW更新检查此值的需要，这似乎是过度的。

   - 添加新列以存储响应者的时间完成
 ALTER TABLE tbl_pXXXX ADD COLUMN complete_time timestamp without time zone; 
 
 
  - 函数来插入时间戳
 CREATE FUNCTION completed_timestamp（）返回触发AS $$ 
语言PLPGSQL 
 BEGIN 
如果新。 complete_flag = 1 and old.completed_flag = 0 
 THEN UPDATE tbl_pXXXX 
 SET complete_time = current_timestamp 
 END; 
 $$; 
 
  - 触发器调用的函数
创建TRIGGER update_timestamp 
 UPDATE ON tbl_pXXXX 
 FOR EACH ROW 
 EXECUTE PROCEDURE completed_timestamp AFTER（）; 
  

解决方案

只要您只更改行触发了触发器，使用 BEFORE UPDATE 触发器可以更简单（也更便宜），它可以更改 new.complete_flag ，然后将其保存到表中，而不必在它已经存在之后运行 UPDATE 语句。

您还可以使用 UPDATE OF< column> postgresql.org/docs/9.4/static/sql-createtrigger.html相对= nofollow> CREATE TRIGGER 语句。

最终结果如下所示：

  CREATE FUNCTION completed_timestamp（）RETURNS trigger AS $ $ 
 LANGUAGE PLPGSQL 
 BEGIN 
如果new.complete_flag = 1和old.completed_flag = 0 THEN 
 new.complete_time：= CURRENT_TIMESTAMP; 
 END IF; 
 RETURN new; 
 END; 
 $$; 
 
创建TRIGGER update_timestamp 
之前ON tbl_pXXXX 
 FOR EACH ROW 
 EXECUTE PROCEDURE completed_timestamp completed_flag OF UPDATE（）; 
  

BACKGROUND: we have a completed_flag that is default 0 and updates to 1 when a person completes a survey. I want to record the timestamp this update happens

After writing this trigger/function to update a timestamp when a flag gets triggered from 0 to 1, I am questioning whether I did this the best way. I suspect it is not the best way to do this but I am somewhat new to postgres. Can the trigger call the function only when that value changes? That would eliminate the need for checking this value every ROW update, which seems excessive

-- 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();

解决方案

As long as you're only making changes to the row which fired the trigger, it's far simpler (and cheaper) to use a BEFORE UPDATE trigger, which can make changes to new.complete_flag before it's saved to the table, rather than having to run an UPDATE statement after it's already in there.

You can also limit the trigger to updates of a particular field with the UPDATE OF <column> clause of the CREATE TRIGGER statement.

The end result would look something like this:

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();

这篇关于postgresql - 触发器，更新字段更新的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！