PostgreSQL - Row Update trigger not firing

Databases
1

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.

2

You are using a AFTER UPDATE trigger, which executes after your row was modified. You can still use it to modify other tables or do logging etc.

Change it to a BEFORE UPDATE trigger and it should work as intended.

A very good description is provided here:

1 Like
3

Works like a charm, you made my day. Thanks @Louis_Brauer1 !

1 Like