Kind of OT:
In a db, timestamps shall be set server-side.
Having a db with a table ‘people’. This table has a column ‘modified_ts’ defined as:
-- Column: public.people.modified_ts
-- ALTER TABLE IF EXISTS public.people DROP COLUMN IF EXISTS modified_ts;
ALTER TABLE IF EXISTS public.people
ADD COLUMN modified_ts timestamp without time zone;
The trigger function ‘update_modified_ts()’ is defined as:
-- FUNCTION: public.update_modified_ts()
-- DROP FUNCTION IF EXISTS public.update_modified_ts();
CREATE OR REPLACE FUNCTION public.update_modified_ts()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
NEW.modified_ts = now();
RETURN NEW;
END;
$BODY$;
ALTER FUNCTION public.update_modified_ts()
OWNER TO torsten;
The trigger on table ‘people’ is defined as:
-- Trigger: update_ts
-- DROP TRIGGER IF EXISTS update_ts ON public.people;
CREATE TRIGGER update_ts
AFTER UPDATE
ON public.people
FOR EACH ROW
EXECUTE FUNCTION public.update_modified_ts();
A SQL statement like ‘UPDATE people SET lastname =‘Meier’ WHERE last name = Mueller’;’ executes and replaces Mueller by Meier, but the trigger does not fire and ‘modified_ts’ is not set.
I don’t see what is wrong with the above code.
Using Postgres.app v2.5.4 and PostgreSQL 14.4.