Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Your question belongs to stackoverflow. –  Milen A. Radev Jul 19 '11 at 14:21

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
thank you khaled, i worked with NEW before but when dealing with char types u need to add a bunch of quotes and pipes to work. –  ahmad badawi Jul 20 '11 at 8:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.