[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

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.
0
matango
Asked:
matango
  • 2
2 Solutions
 
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
 
NievergeltCommented:
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
 
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
 
NievergeltCommented:
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now