#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep Power
    0

    Angry Triggers & Functions, ahhhh....


    Here's the dilemma, I'm extremely new to this PostgreSQL and I'm having to implement a lot of triggers which I have never done in any sort of database setting.

    I've seen that the popular thing to do with triggers is just to call a separate function to handle the actual work of the trigger. Not a big deal, I've gotten that far. Question is, once inside a function, how do you reference tables outside the one you are trying to update or insert into? I am having 2 separate IDs that both reference different instances of another table. if that was too confusin, which it is to me, I have one table called "Individual" that has a primary key as the "I_ID." I have a table called "Married_Into" that accepts "I_ID_1" and I_ID_2" and a family ID, but that's not a big deal. Problem is, upon insertion or update of "Married Into" table I need to check the genders of the 2 individuals because gay marraige just ain't right ;-) Anyway, I need to be able to reference the Individual table and reference I_ID two separate times by using I_ID_1 and I_ID_2. So, how can I reference Individual table even though the data is actually a tuple of the Married_Into table?

    Thanks in advance for any and all help. I am in need of a miracle about now.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    11
    create a CURSOR that is based on a SELECT from the outside tables that the record is not INSERT/UPDATE-ing to. You can create a REFCURSOR that will be a query def. for the cursor that you define based on the record that is being INSERT/UPDATE-ed

    DECLARE

    some_refcursor REFCURSOR;
    v_I_ID_1 VARCHAR(10);
    v_I_ID_2 VARCHAR(10);

    BEGIN

    OPEN some_refcursor FOR SELECT gender FROM gender_table WHERE I_ID_1 = NEW.I_ID_1;

    FETCH some_refcursor INTO v_I_ID_1; --now you've got the gender of the one;

    CLOSE some_refcursor;
    OPEN some_refcursor FOR SELECT gender FROM gender_table WHERE I_ID_2 = NEW.I_ID_2;
    FETCH...


    on like that, this is how I would do it in PL/pgSQL

    remember to install the language handler function or use createlang plpgsql from your shell.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    12
    What about this?

    DECLARE
    v_I_ID_1 VARCHAR(10);
    v_I_ID_2 VARCHAR(10);
    BEGIN
    SELECT INTO v_I_ID_1 gender FROM gender_table WHERE I_ID_1 = NEW.I_ID_1;

    SELECT INTO v_I_ID_2 gender FROM gender_table WHERE I_ID_2 = NEW.I_ID_2;
    END;

IMN logo majestic logo threadwatch logo seochat tools logo