CODEWITHSUNDEEP
postgresqlplpgsql
KlauRau
KlauRau

Reputation: 71

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

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,

Upvotes: 0

Views: 2798

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3313

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

Upvotes: 1

Related Questions