inserting data of two forms into two tables with same transaction id

[rammie singh]

i have two forms and i am fetching the data from those two forms. i want to store the data of these two forms in two different
tables.

the problem is i want to maintain a common column taht is tranaaction_id , in both tables. also if i want this transaction_id to be generated dynamically i.e. my databse should generate it.


also both form data need to be inserted together. that is either both are inserted or none are inserted.

i am using MYSQL database.

can any one please suggest me the solution.

Thanks and regards.

[Campbell Ritchie]

How about a 3rd table for transaction_id with an auto_increment (in MySQL try "serial") column in as a primary key, then the other two tables can refer to this table as a foreign key??

[Paul Sturrock]

Sounds like you need a transaction log table that stores these ids and has a relationship with both tables. Then if you wrap your insert into a transaction (making sure you are using one of the database engines that support transactions) you should get the behaviour you need.

[Swastik Dey]

This might be a solution, let me know if I am going wrong

 

try{
	connectionObj.setAutoCommit(false);
	//we try insert in the first table 
	//we try insert in the second table
	//if both inserts are successful we commit the transaction
	connectionObj.commit();
}

catch(SQLException e){
	//if any of the insert fails we roll back the transaction
	connectionObj.rollback();
}
   

[Campbell Ritchie]

Would you add setAutocommit(true) to the catch??

[Swastik Dey]

Probably yes, may be for the other transactions.