Order of Operation for SQL

It sounds like item_id is a key value you are incrementing by hand. If this is the case, this should be managed by a before insert trigger and sequence. If done that way, then you shouldn't have an issue. Have you tried the "returning item_id into variable" clause of the insert statement? That will give you the current id back. However, if you are manually maintaining a PK you should stop and look into before insert triggers and sequences to handle the item id value as I said before because these will always give you a unique value. If this does not provide you with enough information then please describe what your trying to do with item_id alittle better as it is difficult to tell if it is a link between tables a and b or if it is just a PK on one of the tables.











From what I get from your post, it sounds like you have (please correct me if I am wrong):



A Detail Table of some kind (Table A) about the file or maybe a index of some kind.



A Storage table (Table .







Item_ID which is an identification number of some kind on table b that you need in an associated record in table a.







Here is what I would do:







<ol>


<li>Have Item_ID be generated automatically by a trigger.</li>

<li>Insert into table B first using a returning clause to get the item_id and store it into a tmp variable.</li>

<li>Insert into table A using the item_id returned from the insert into table b.</li>
</ol>
Just a note: you will need to repete steps 2 and 3 for each file or put them all as part of the same record and use one insert statement.

Edited by: David Pulliam on Nov 4, 2008 1:57 PM