simple Postgres Trigger function on update not triggering

Question

Probably something dumb, but I just can't make it work. I have this table:

                                      Table "public.office"
   Column    |           Type           | Collation | Nullable |             Default
-------------+--------------------------+-----------+----------+-------------------------------
 id          | integer                  |           | not null | generated by default as identity
 name        | text                     |           | not null |
 url         | text                     |           |          |
 domain      | text                     |           |          |

...
Triggers:
    updated_save_domain_from_url AFTER UPDATE OF url ON office FOR EACH ROW EXECUTE PROCEDURE save_domain_from_url()

And this trigger function:

-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION public.save_domain_from_url()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  new.domain := substring(new.url from '(?:.*://)?(?:www\.)?([^/]*)');
  RETURN new;
END;
$function$

Example row:

 id  |              name               |  url   | domain
-----+---------------------------------+--------+--------
 425 | Van Halewyck & Marco Architects | [NULL] | [NULL]

When I update the URL:

update office set url = 'http://vanhalewyck-marco.com/en' where id = 425;

The domain is still null, trigger did not work:

id  |              name               |               url               | domain
-----+---------------------------------+---------------------------------+--------
 425 | Van Halewyck & Marco Architects | http://vanhalewyck-marco.com/en | [NULL]

Any hints of what might be going on, please help!!

Thanks

Bjarni Ragnarsson · Accepted Answer · 2019-12-21 06:16:32Z

1

You need to have the trigger defined as BEFORE trigger. AFTER triggers (as yours) cannot alter the new record as they are executed after the record is inserted.
Best regards,
Bjarni

answered Dec 21, 2019 at 6:16

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

simple Postgres Trigger function on update not triggering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related