Results 1 to 2 of 2

Thread: defer

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unanswered: defer

    Hi Again!

    Er right, I have two tables, both which have foreign key constraints on eachother.

    Basically I know it is possible to defer a constraint: I wish to defer it for the duration of a trigger described bellow:

    table A gets a new record
    on insert trigger goes off and inserts a record into table B (therefore the constraint is valid)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: defer

    Originally posted by rhs98
    Hi Again!

    Er right, I have two tables, both which have foreign key constraints on eachother.

    Basically I know it is possible to defer a constraint: I wish to defer it for the duration of a trigger described bellow:

    table A gets a new record
    on insert trigger goes off and inserts a record into table B (therefore the constraint is valid)
    Hi again. It should work OK. Here is an example I just tried using 2 tables called P (parent) and C (child):

    SQL> create table p(p_id number primary key);

    Table created.

    SQL> create table c( c_id number primary key, p_id number not null);

    Table created.

    SQL> alter table c add constraint c_p_fk foreign key (p_id)
    2 references p(p_id) deferrable initially deferred;

    Table altered.

    SQL> create trigger c_trg after insert on c
    2 for each row
    3 begin
    4 insert into p(p_id) values (:new.p_id);
    5 end;
    7 /

    Trigger created.

    SQL> insert into c(c_id,p_id) values (2,1);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from p;

    P_ID
    ----------
    1

    SQL> select * from c;

    C_ID P_ID
    ---------- ----------
    2 1

Posting Permissions

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