Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    3

    Unanswered: Sequences, Triggers and Foreign Keys - OH MY!

    I'm a novice thrown to the wolves here!

    I have two tables (for example) for which I've set up Sequences and triggers to create Primary keys for each. Now I have to create a foreign key relationship between these two tables and would like to populate the foreign key field in the "child" table utilizing a trigger as well.

    Can anyone give me an example of how I might do this?

    Do I add more code to the already present trigger loading the parent table primary key? If so, what's the most efficient coding for that?

  2. #2
    Join Date
    Jun 2002
    Location
    Indianapolis
    Posts
    21
    You are correct. You can do that. There are a few ways to do this, triggers are one of them.

    Here is a sample. There are no exception blocks!!!

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production

    SQL> create table foo_p(foo_p_key number not null constraint pk_foo_p primary key, data1 varchar2(100));
    Elapsed: 00:00:00.51
    SQL> create table foo_c(foo_p_key not null constraint fk_foo_p references foo_p, data1 varchar2(100));
    Elapsed: 00:00:00.71
    SQL> create sequence seq_foo_key increment by 1 start with 10000;
    Elapsed: 00:00:00.30
    SQL> ed
    Wrote file afiedt.buf

    1 create or replace trigger tr_foo_p_key_bir
    2 before insert
    3 on foo_p
    4 for each row
    5 begin
    6 select seq_foo_key.nextval
    7 into :new.foo_p_key
    8 from dual;
    9* end;
    SQL> /
    Elapsed: 00:00:00.20
    SQL> ed
    Wrote file afiedt.buf

    1 create or replace trigger tr_foo_p_key_air
    2 after insert
    3 on foo_p
    4 for each row
    5 begin
    6 insert into foo_c(foo_p_key,data1) values(:new.foo_p_key,:new.data1);
    7* end;
    SQL> /
    Elapsed: 00:00:00.30
    SQL> create or replace trigger tr_foo_p_key_air
    2 after insert
    3 on foo_p
    4 for each row
    5 begin
    6 insert into foo_c(foo_p_key,data1) values(:new.foo_p_key,:new.data1);
    7 end;
    8 /
    Elapsed: 00:00:00.40
    SQL> insert into foo_p (data1) values ('are we having fun yet?');
    Elapsed: 00:00:00.20
    SQL> select * from foo_p;

    FOO_P_KEY DATA1
    ---------- ----------------------
    10001 are we having fun yet?
    Elapsed: 00:00:00.30
    SQL> select * from foo_c;

    FOO_P_KEY DATA1
    ---------- ----------------------
    10001 are we having fun yet?
    Elapsed: 00:00:00.40
    SQL>


    Hope this helps

  3. #3
    Join Date
    Jun 2002
    Posts
    3

    Thanks...with a follow-up question

    I see that you're using a Before Insert for setting the primary key of the parent table, followed with an After Insert for setting the foreign key of the child table. Is there any reason why you wouldn't combine the setting of the foreign key into the same Before Insert trigger with the setting of the Primary Key?

    Thanks for your help.
    fordo

  4. #4
    Join Date
    Jun 2002
    Location
    Indianapolis
    Posts
    21
    Well, I apologize for my example. I see that I doubled up my air trigger, and I goofed on the fk creation. Drop the constraint in the foo_c table and recreate it correctly as a fk if you are using my example.

    In answer to your question, my first suggestion is to try it! Drop the air trigger and modify the bir one. Give it a whirl. See what will happen.

    -5 minutes later-

    Great! Did you see what happened? The trigger bombed because the code is being executed before the actual insert into the foo_p table is performed because the parent record will not exist when you try to insert the child record. If you put it in an after trigger, the parent record will exist, and the child insert will succeed.

    HTH.
    -- Brice

  5. #5
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    31
    for reference if you encounter the dreaded table mutation error (ORA-04091) whilst using triggers it's because you are refering to a table row that is being written to at the same time that you're trigger is trying to read it.

    always catches people that one
    My homepage:
    http://www.buro9.com/
    My work:
    http://www.btopenworld.com/
    http://www.officialfootballsites.co.uk/
    http://www.jeepster.co.uk/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •