0

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!

Mike Organek
  • 11,647
  • 3
  • 11
  • 26
Torrino
  • 163
  • 13

1 Answers1

0

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();
Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • This is perfect but one question; what would I do if the primary key in the row that I want to delete is a composite key of 2 columns? – Torrino Aug 01 '20 at 14:02
  • @TorrinoMcQueen You can reference both (or all if more) of the columns using the `NEW` row. e.g. `where price = 0 and id1 = NEW.id1 and id2 = NEW.id2` – Mike Organek Aug 01 '20 at 14:21