Best practice for inserting many to many records

[zensunni]

This is the way I do this:

Tables:
Departments
Departments_to_ employees
Employees

Add employee function:
-Lock employee table
-Insert employee
-Search for last employee record to find newly added employee ID
-Unlock employee table
-Insert employee id and department id into Departments_to_ employees table

There has got to be a better way of doing this. Is using stored procedures the only way?

[Dave44]

ya what you are using will work fine. you dont need to lock the table though, with proper foreign keys in place oracle will handle that for you. also just return the new employee ID into a variable then you dont need to search for it.

[debasisdas]

Is using stored procedures the only way?

Probably that is the best way.

[zensunni]

Thanks for the help.

also just return the new employee ID into a variable then you dont need to search for it.

How would I do this? Can I get the insert statement to return fields after it has been executed?

I'm using ASP.

[r035198x]

How about using a trigger?