multiple interdependable queries in one button click

Question

In my application I had a form where i have to do more than one database transaction within a single button click ie When I click button

Query 1 >>>insert designation values to Designation table

query 2. >>select query to get the primary key of last entered designation.... ie .the auto number against which the above insert query inserts

query 3 >> inset data into another table with the primary key recvd from above

query 4 .>> update another table with the primary Key Above

If any of the query fails or any exception occur The others shouldnot work

now I am trying to do it using try catch and deleting prevoius insertions if exception occurs...

Is there any good Ideas as I am new to sql server 2008

Using transactions is Your answer. Please post the code for the "button_click". It will enable answering Your question more easy. — Marty, Sep 10 '12 at 11:43
You can code all of the above into a single stored procedure. — Oded, Sep 10 '12 at 11:44
can you pls give a link to such a prodedure using multiple queries — Sreenath Ganga, Sep 10 '12 at 11:49

score 1 · Answer 1 · edited May 23 '17 at 11:49

As others have commented, wrap all these operations into a single transaction, instead of using separate transaction for each of them. The "atomicity" part of the ACID properties of transactions will ensure either all or none of the operations will succeed, and you can never have a partial result.

If you are using ADO.NET, you'll be using BeginTransaction and Commit for that.

BTW, you should not do the step 2 - what if a concurrent client inserts a row (and commits) after your step 1 but before you had a chance to execute step 2? You'll get the wrong PK. It's better to directly get the PK generated in step 1.

multiple interdependable queries in one button click

1 Answers1