I'm trying to write a trigger (on insert or update) on one table that will
update values in another table, and I'm having trouble with it. There are 3
tables in question, person, household, and person_household (linking people
to households). There are foreign key constraints on the person_household
table, if that matters, but no triggers on any table except person, which
is the one I'm working on. I'm dealing with parents and kids, and a parent
will only be in one household.
I can write SQL statements that will work outside of the trigger to get me
the records I want, but they don't seem to work correctly within the
trigger. I've put my pseudo code place holders in # signs.
The fist thing I'd need to do is
SELECT id_household
FROM person_household
WHERE id_person=NEW.id_person --find the household that person belongs to
with that ID I get, then do the following
--get the other parent in the household (if any)
SELECT id_person, name_first
FROM person NATURAL INNER JOIN person_household
WHERE (person_household.id_household=#id_household (from above query)# AND
person.grade IS NULL) --leave out the kids
then with that name I get, I need to get the household record and update it:
IF FOUND THEN
UPDATE household
SET salutation = NEW.first_name || #person.name_first selected in
2nd query above)#
WHERE id_household = #id_household#
END IF;
This works in theory, but not in practice. I've tried SELECT INTO as well,
also no luck. I could write this in my sleep in a different language, but
I'm new to plpgsql, and this is kicking my, well, you know. Thanks for any
help.
-Diana
Diana Nemirovsky
Marin Consulting, Inc.
770 Menlo Ave, Ste 223
Menlo Park, CA 94025
v (650) 617-8699
f (650) 833-0790
http://www.marinconsulting.com