CODEWITHSUNDEEP
sql
slaw
slaw

Reputation: 621

Multiple SQL commands that should only execute if they all pass

So I want to do 2 SQL inserts into different tables.

One insert into table A, one insert into table B.

I insert into table A first, and then in to table B.

I want to make sure that if for whatever reason the insert into table B fails (which is never should) then the insert into table A is rolled back.

What's the recommended way to do, also why do I never seem to see this in any example code?

Thanks

Upvotes: 2

Views: 49

Answers (2)

Rahul
Rahul

Reputation: 77876

You need to use Transaction and that's why transaction is for. To provide atomacity. A sample code would be

create procedure usp_insert
as
begin

begin transaction
insert into tableB ....;
insert into tableA ....;

commit;

if error
rollback;
end

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269863

This is called a transaction and most databases support explicit transactions.

Sample syntax is:

begin transaction
    insert into tableA . . . 
    insert into tableB . . .
commit transaction;

The specific syntax might vary by database. But the meaning is the same: either the entire transaction succeeds or it fails.

Upvotes: 3

Related Questions