1

Say I have a table that is has a primary key like:

product
-id
-modified_at

There are other tables that have the product_id as a foreign key:

inventory
-id
-product_id

options
-id
-product_id

I want to update the product.modified_at column every time there is an update to any of the other tables inventory and options.

Can I use a trigger to do this?

update product set modified_at=NOW() where id=@product_id

klin
  • 112,967
  • 15
  • 204
  • 232
Blankman
  • 259,732
  • 324
  • 769
  • 1,199
  • Yes, you can do just that. It's `NEW.product_id` though not `@product_id`, assuming you are writing the trigger function using pl/pgsql. – Bergi Apr 14 '23 at 22:21

1 Answers1

1

Use the record new in the trigger function, e.g.

create or replace function before_update_on_inventory_or_options()
returns trigger language plpgsql as $$
begin
    update product 
    set modified_at = now() 
    where id = new.product_id;
    return new;
end $$;

You can use this function in multiple table triggers:

create trigger before_update_on_inventory
before update on inventory
for each row 
execute function before_update_on_inventory_or_options();

create trigger before_update_on_options
before update on options
for each row 
execute function before_update_on_inventory_or_options();

You can define a condition that determines whether the trigger function will actually be executed, e.g.:

create trigger before_update_on_inventory
before update on public.inventory
for each row 
when (new.product_id is not null)
execute function before_update_on_inventory_or_options();

Note: in Postgres 10 or earlier, the syntax was execute procedure .... In versions 11+, procedure is deprecated in favor of function.

klin
  • 112,967
  • 15
  • 204
  • 232
  • if I am performing bulk operations on the `options` or `inventory` table, can I tell postgresl to ignore the trigger explicitly somehow? – Blankman Apr 16 '23 at 03:36
  • Yes. [How do I temporarily disable triggers in PostgreSQL?](https://stackoverflow.com/q/3942258/1995738) – klin Apr 16 '23 at 11:04
  • what is $$ for? – Blankman Apr 17 '23 at 15:23
  • any reason this is a Before trigger? could it be and AFTER also or before is better in this scenerio? – Blankman Apr 17 '23 at 15:54
  • 1
    In this particular case, there is no difference between before and after triggers. Postgres `$$` is a special quotation mark, see [What are '$$' used for in PL/pgSQL](https://stackoverflow.com/a/12172353/1995738) – klin Apr 17 '23 at 16:08
  • last comment... what if the trigger is added to a table where product_id is nullable, so I only want the trigger to fire when it is not null? – Blankman Apr 17 '23 at 18:01
  • 1
    Actually, the trigger will work well but you might use the formal `when` clause in the trigger definition (see the updated answer) – klin Apr 17 '23 at 18:35