Reputation: 139
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
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