CODEWITHSUNDEEP
sql-servervb.net
user164012
user164012

Reputation: 11

inserting values into master and transaction table at the same time

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..

Upvotes: 1

Views: 580

Answers (3)

anishMarokey
anishMarokey

Reputation: 11397

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

Upvotes: 0

KM.
KM.

Reputation: 103637

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425663

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

Upvotes: 2

Related Questions