Postgresql trigger function that checks if data that's been updated in a single column is equal to something

Question

I have a single table A with 3 columns id, name, price. I'm trying to create a trigger in postgres that fires when the value under price is updated/set to 0. If the value is 0, then the entire row is deleted.

I'm a complete beginner in postgresql but I desperately need this for my node.js project. Any help is greatly appreciated!

Answer 1

This is fairly straightforward.

The first step is to create the trigger logic. This example deletes the row from a if price = 0. The id = NEW.id restricts this deletion to the row that just changed.

create or replace function zero_price_delete() 
returns trigger language plpgsql 
as $$
begin
  delete from a where price = 0 and id = NEW.id; 
  return new;
end; $$;

Then assign this as an after trigger on A following insert or update operations.

create trigger zero_delete 
 after update or insert on a 
 for each row 
 execute procedure zero_price_delete();