MS SQL Atomic Inserts

Hi all,

I'm running an ASP script where I'm trying to insert data into 6 tables.
Now, if even ONE of these insert operations fails, then I want to rollback all of the successful inserts up to that point

So far, the SQL code I have looks like this:
BEGIN TRANSACTION xact
INSERT INTO tableA values('a','1')
INSERT INTO tableB values('a','2')
INSERT INTO tableC values('a','3')
INSERT INTO tableD values('a','4')
INSERT INTO tableE values('a','5')
INSERT INTO tableF values('a','6')
COMMIT TRANSACTION xact

I don't know how to get SQL to do something like:
if there were any errors, then ROLLBACK TRANSACTIOn xact, else COMMIT TRANSACTION xact.

Is there any way to do this without using stored procedures or triggers?

Thanks very much for any assistance.
matangoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hammadian2Commented:
BEGIN TRANSACTION xact
INSERT INTO tableA values('a','1')
INSERT INTO tableB values('a','2')
INSERT INTO tableC values('a','3')
INSERT INTO tableD values('a','4')
INSERT INTO tableE values('a','5')
INSERT INTO tableF values('a','6')

IF @@ERROR <> 0
BEGIN
  COMMIT TRANSACTION xact
  RETURN 0
END
ELSE
BEGIN
  ROLLBACK TRANSACTION xact
  RETURN @@ERROR
END
0
NievergeltSenior SW DevCommented:
Hammadian2's answer is essentially correct, but @@ERROR returns the error number for the *last* Transact-SQL statement executed. His suggestion will only work, if the error occurs with the last INSERT.

Therefore you either need to bracket all but the first INSERT with an IF (more efficient) or save @@ERROR (or whether it was not zero)) for each insert in a variable and then change the expression in the IF to include all results (ANDed).

I would suggest:

DECLARE @had_error int

.....

SELECT @had_error = 1
BEGIN TRANSACTION xact
INSERT INTO tableA values('a','1')
IF @@ERROR <> 0
BEGIN
  INSERT INTO tableB values('a','2')
  IF @@ERROR <> 0
  BEGIN
    INSERT INTO tableC values('a','3')
    IF @@ERROR <> 0
    BEGIN
      INSERT INTO tableD values('a','4')
      IF @@ERROR <> 0
      BEGIN
        INSERT INTO tableE values('a','5')
        IF @@ERROR <> 0
        BEGIN
          INSERT INTO tableF values('a','6')
          COMMIT TRANSACTION xact
          SELECT @had_error = 0
        END

IF @had_error <> 0
BEGIN
  ROLLBACK TRANSACTION xact
END
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matangoAuthor Commented:
Thanks very much for your help, guys. (:

Just a few points though:
- @@ERROR returns 0 if the transaction completed successfully ... perhaps you both thought it was the other way around, since you used the inequality operator
- you use SET not SELECT to assign a value to a variable


I've used your ideas and come up with the following, which works fine.

---
declare @@had_error int
set @@had_error = 1
BEGIN transaction xact

INSERT INTO test VALUES ('a', '0')
if @@ERROR = 0
begin
      INSERT INTO test VALUES ('b', '1')
      if @@error = 0
      begin
            set @@had_error = 0
      end
end

if @@had_error = 1
BEGIN
      ROLLBACK transaction xact
END
ELSE
BEGIN
      COMMIT transaction xact
END
0
NievergeltSenior SW DevCommented:
Sorry about the @@ERROR - that's what you get with copy/pasting.

Setting local variables works nicely, though; try it.

Glad to have been able to help you    Christoph
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.