Determining the specific sequence number generated on an insert statement

Hi,

I have a question about determining the specific sequence number generated on an insert statement.

Suppose I have a table A that has the following structure   A { A_ID number primary key,

      A_column2 number }
and a table B that references A_ID as a Foreign Key

  B { B_ID number,
      A_ID number, 
      B_column2 number }

Using an INSERT BEFORE EACH ROW trigger and a sequence called A_seq, an unique number is generated for A_ID each time a row is inserted into table A.

Now, I create a view based on table A and B as follows

  CREATE VIEW ab_view (A_ID, A_column2, B_ID, B_column2)   as select a.A_ID, a.A_column2, b.B_ID, b.B_column2   FROM A a, B b
  WHERE a.A_ID = b.A_ID;

I want to be able to insert using this view, so I used an INSTEAD OF trigger as such

CREATE TRIGGER AB_trigger INSTEAD OF INSERT ON ab_view FOR EACH ROW
DECLARE
  aid number;
BEGIN
  INSERT INTO A (A_column2) values (:new.A_column2);

/* How do I find out what the exact value of A_ID is

     in the above insert statement? */

  aid := A_seq.currval;

/* This value might not correspond to the exact value

     in the above insert statement if this is a highly
     concurrent system, no? */

  INSERT INTO B (A_ID,B_column2) values (aid, :new.B_Column2);
/* It is assumed that B_ID is generated using another sequnece B_seq */

END; So, what is the best way to ensure that the value inserted into B.A_ID corresponds exactly to the value A.A_ID generated using a sequence in the first INSERT statement?

Any comments or suggestsions would be greatly appreciated. If possible, please cc your reply to lpoon_at_isr.umd.edu.

Leon Poon Received on Tue Dec 08 1998 - 12:53:30 CST