Thread: plpgsql trigger problem

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

> 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.
>

Give us the real code of the function, plz

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)