> 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 ;)
