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