• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How can I insert a record in two tables with the same id?

 
Jhon Parker
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have 3 tables: Salesman, Car, and a many to many table with those two tables named Salesman_CAR


i have a web page that is called create new car and sell it, with a button at the bottom, and when that button is pressed i need to insert a record in the Car table with the data that was submitted, and also I need to insert two ids in the many to many table "Salesman_CAR" those ids are: the one I just created in the page when i pressed the button this id is the " CAR ID" from he CAR table; and the other one is the "salesman id" i get these "salesman_id" with a method that give me the id of the person that is logged in the web site.


My problem is that i have a sequence created that generate the id of the CAR and when i insert the car record after i press the button i auto-increment the id with the sequence, so when i want to insert in the many to many table i lose the id of the CAR, i can't use a query to get the last id of the car table because this is a web application used by a lot of users, so the last id inserted in the car table couldn't be the same id for the same user that inserted the car.

is there a way to achieve this.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sequences (generally) allow a neat trick: you can use nextval to generate a new id, or currval to return last generated id in your session. So, you could do something like this (Oracle's syntax):
This creates two records using the same ID. No need to read and reuse the ID.

However, you won't get the ID back this way. In Oracle, you could use a returning clause to get back the ID generated in the insert statement in a single database call.

Another possibility would be to read the nextval value (eg. select mysequence.nextval from dual), read it in application and use it (or better, the currval) for subsequent inserts. And finally, you could enclose all this functionality in a stored procedure and use just one DB call for this entire business.

Read about the nextval and currval in your DB documentation. The syntax in other databases with sequences (Postgres, for example) may be different.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic