inserting values into master and transaction table at the same time

Question

Hi,

After inserting into masterTable it returns an ID. With that ID I want to enter more than one row into a transaction table.

I am using two separate procedures. The problem is, after inserting the record into master, and while inserting into the transaction table, if any interruption occurs I want to abort the corresponding insert of the master table.

Can I do it with using one stored procedure?

Please help..

Answer 1

Do it inside a transaction:

BEGIN TRANSACTION
EXECUTE prc_insert_master @value
EXECUTE prc_insert_child @value, @result OUTPUT
IF @result = -1 THEN
BEGIN
    ROLLBACK
END
ELSE
    COMMIT

Answer 2

to group multiple database data modifications commands into a "all or nothing" set of work, use a transaction.

Any and all data modifications following a BEGIN TRANSACTION can be completely reversed, as if they never happened, when you issue a ROLLBACK. They can all be made permanent by issuing a COMMIT.

You can use the application language or TSQL, within a procedure, to issue the BEGIN TRANSACTION, COMMIT, and ROLLBACK commands based on return values, error trapping, or any other logic you wish.

Answer 3

use transaction .read this for more information on sql transaction.