Trigger function to update timestamp attribute when any value in the table is updated

Question

I want to update the timestamp attribute 'register_updated' to the current_timestamp whenever any value is updated in the table.

This is my function

CREATE OR REPLACE FUNCTION fn_register_updated() 
RETURNS trigger language plpgsql AS $$
BEGIN
    UPDATE tb_register
    SET register_updated = CURRENT_TIMESTAMP
    WHERE (OLD.* IS DISTINCT FROM NEW.*);
    RETURN NEW;
END;
$$;

CREATE TRIGGER tg_register_updated
BEFORE UPDATE 
ON tb_register 
FOR EACH ROW 
EXECUTE PROCEDURE fn_register_updated();

But whenever I run an update on a table I receive the following error:

SQL statement "UPDATE tb_register
    SET register_updated = CURRENT_TIMESTAMP"
PL/pgSQL function fn_register_updated() line 3 at SQL statement
SQL statement "UPDATE tb_register
    SET register_updated = CURRENT_TIMESTAMP"
PL/pgSQL function fn_register_updated() line 3 at SQL statement
SQL statement "UPDATE tb_register

Any ideas on how to solve this?

I am struggling with the use of UPDATE within the body of the function.

Thank you,

You don't want to do this as updating via a trigger on an update will set up a recursion(which you are seeing in the error message) and will not succeed. What you want is `NEW.register_updated = CURRENT_TIMESTAMP;` instead of the `UPDATE`. — Adrian Klaver, Dec 07 '21 at 23:57
@AdrianKlaver thank you! So, using the UPDATE statement within the function to update a column after a table is updated is not possible? — KlauRau, Dec 08 '21 at 08:44
I have read here: https://stackoverflow.com/questions/14137932/update-a-table-with-a-trigger-after-update, that it is possible to avoid recursion by adding a WHERE statement. I have updated my code accordingly, but it I am not sure how to indicate "WHERE any value is updated" — KlauRau, Dec 08 '21 at 08:50
The point is 1) The `UPDATE` is not needed period. 2) You are updating before the `UPDATE`, though it would not matter if it was an `AFTER` trigger. 3) You will still get recursion. The `NEW` record has `register_updated` field just do `NEW.register_updated = CURRENT_TIMESTAMP; RETURN NEW;`. That will change the value of that field in the trigger and not cause the `UPDATE` trigger to be fired again. It really quite simple, don't fight the process. Read this [plpgsql trigger](https://www.postgresql.org/docs/current/plpgsql-trigger.html). — Adrian Klaver, Dec 08 '21 at 16:35

score 1 · Answer 1 · answered Dec 08 '21 at 00:23

Sample code for you:

CREATE OR REPLACE FUNCTION fn_register_updated() 
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
    new.register_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$function$;


create trigger tg_register_updated before
update
    on
    tb_register for each row execute function fn_register_updated();

Trigger function to update timestamp attribute when any value in the table is updated

1 Answers1