Hi Friends,
I am having having a table t_questions (Parent Table) and t_answers (Child Table)in oracle 11g.
t_questions
QID - Number(10) (Primary Key)
Question- Varchar2(100)
t_answers
AID- Number(10)
Answer- Varchar2(100)
QID - Number(10) (Foreign Key)
I developed front for adding question and multiple answers to the question in the same screen and saving it using SAVE button once ( eg, Q1. What is your Favorite color? Ans.1 Green, Ans.2 Red, Ans.3 Yellow). While inserting I used sequence.nextval to populate Primary key for QID( i,e 1), I inserted the question in the t_question table and using the same QID i tried to insert into the foreign key column in the t_answers table.
But I am getting error, in the answer table its trying to insert the QID value as 2, sequence.nextval is called again and value is incremented.
class User extends QActiveRecord
{
public $sequenceName = 'USERS_SEQ';
…
If i try to use sequence.currval, I am having doubt, before fetching the currval, there may be concurrent users who will try to insert the record in the t_question table, I fear I may get irrelavant value.
My DB team is not permitting to use trigger to insert the primary key value
Kindly help me to solve this issue.