postgresql trigger for updating multiple columns

Question

i have a column in a certain table that i want to match to a column in a different table in a postgresql database, so i tried creating a trigger that could accomplish that:

CREATE OR REPLACE FUNCTION modify_res_partner() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
UPDATE res_partner
SET x_street1=res_partner_address.street
FROM res_partner_address
WHERE res_partner.id=res_partner_address.partner_id;
END IF;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER modify_res_partner_trigger
AFTER INSERT OR UPDATE OR DELETE ON res_partner_address
FOR EACH ROW EXECUTE PROCEDURE modify_res_partner();

the 2 tables share the same value of partner_id for each record, but as you may have guessed, this trigger executes queries for every true condition, so when i modify one record on the first table, the trigger causes 800 (for example) modification to occur for every matching partner_id pair.

how can i modify the record alone that matches the modified record on the first table?

Answer 1

You need to change your query to become like this:

UPDATE res_partner
SET x_street1=res_partner_address.street
FROM res_partner_address
WHERE res_partner.id = NEW.partner_id;

You should make use of the NEW row. This refers to the inserted row in case of INSERT operation. It also refers to the updated row in case of UPDATE operation.

By the way, you don't need to create the trigger for DELETE operation.