How to write stored procedure for inserting into many to many relationships
I have two tables: User and Project each with a integer PK of "id" and single data field of "name".
I have created a junction table ProjectsUsers that implements the many to many relationships between Users and Projects.
Can someone help me write a stored procedure to, given a User.id and a new project name, create a new row in the project table and update the junction table appropriately so it is owned by the user.
(1) I assume we want to start a new transaction
(2) What failures could occur that would cause me to want to roll back the transaction?
(3) How do I detect failures that would cause me to roll back the transaction? Do I check a global variable for errors? Or do I assume the insert statement will throw an exception I can catch?
(4) How do I get the id of the newly created project row and return it back to the calling program?
(5) How do I communicate to the calling program that the transaction failed and must be tried again?
Thanks
Siegfried