I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need to use transact in the procedure .
The reason why I created a junction table( Invoice_Details_Table) is for the reason that some invoices will have more than one driver with one or more fruits.
For example a trip was done so we need to create an invoice(1). Two drivers went on this trip and delivered Strawberries and Apples.
Trip number two so invoice(2) needs to be created. This delivery was with one driver and only apples were delivered.
The problem that I am having is how to create a procedure that will update the invoice table and the Invoice_Detail_Table and rollback if something happens in the insert process. Also how can I pass the driver/fruit parameters since sometimes it will be more than one.
I am using SQL Server 2012. Any help is much appreciated