CODEWITHSUNDEEP
postgresqlfunctiontriggers
hackg
hackg

Reputation: 139

postgresql - trigger, update timestamp on field 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();

Upvotes: 1

Views: 3465

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21385

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

Upvotes: 1

Related Questions