Basically I have table_a and table_b. table_b is made with features from table_a and in common they share section_id column and status.
table_a's section_id is primary key so it's unique, but table_b can have multiple section_ids, but they all share same status
Users insert and update table_b and I want to capture the status changes back in table_a
CREATE TRIGGER table_b_aiu AFTER INSERT OR UPDATE ON table_b FOR EACH ROW WHEN (((new.status = 100) OR (new.status = 200))) EXECUTE PROCEDURE table_b_aiu(); CREATE OR REPLACE FUNCTION table_b_aiu() RETURNS trigger AS $BODY$ BEGIN UPDATE table_a a SET status = 100 FROM table_b b WHERE (b.status = 100 or b.status = 200) AND a.section_id = b.section_id; RETURN new; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
The problem with this, is that instead of updating just the newly updated or inserted row, it updates the ENTIRE dataset, which is not intended. How to update only new rows? I've tried putting arguments on the triger but it doesn't accept new.section_id