PostgreSQL – Trigger to Update Column on Insert or Update in Another Table

postgresql

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

Best Answer

By default, UPDATE table_a ... FROM table_b builds CROSS JOIN of all rows from both tables. So such UPDATE applies to all rows of table_a.

Solution is simple: add to WHERE condition

AND a.section_id = NEW.section_id

to limit rows of CROSS JOIN result to only just-now-updated rows.

NEW and OLD are a system record which keep values of all columns of row for which trigger function has been called.

Related Question