Hey,

I have a project that I have been working on and I need to insert a
record into multiple tables and I if any one of the inserts fails I
need to rollback all of the previous inserts that were done.  To
illustrate, I have ten tables that need to have a record inserted into
them and if it errors out on table six, then I want to rollback the
previous five inserts.  I'd appreciate any advice I can get.  Thanks.

bradley.d.wal***@gmail.com wrote:
BEGIN TRANSACTION

INSERT Table1 ......
IF @@ERROR <> 0
    BEGIN

    ROLLBACK TRANSACTION
    RETURN

    END

INSERT Table2 ......
IF @@ERROR <> 0
    BEGIN

    ROLLBACK TRANSACTION
    RETURN

    END

INSERT Table3 ......
IF @@ERROR <> 0
    BEGIN

    ROLLBACK TRANSACTION
    RETURN

    END

INSERT Table4 ......
IF @@ERROR <> 0
    BEGIN

    ROLLBACK TRANSACTION
    RETURN

    END

.....
.....
.....
.....
.....
.....

COMMIT TRANSACTION

With SQL 2000 -SQL 2005 is a bit 'simplier' in manifestation - but the idea is the same.

BEGIN TRANSACTION

INSERT INTO into table1 (ColList) VALUES (ValList)
IF @ERROR <> 0
   BEGIN
      ROLLBACK
      RETURN
   END

INSERT INTO into table2 (ColList) VALUES (ValList)
IF @ERROR <> 0
   BEGIN
      ROLLBACK
      RETURN
   END

{etc.}

COMMIT TRANSACTION
RETURN


There are variations to the idea, using GOTO to the end of the sproc and ROLLBACK from there.


Show quoteHide quote

To add to the other responses, consider adding SET XACT_ABORT ON to the
beginning of your proc.  This will ensure a transaction rollback occurs in
the case of a client-initiated attention event (e.g. query timeout or
cancel).

Show quoteHide quote

Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
Very interesting! I did not know about this. This can be a quick fix
for applications that suffers from unhandled query timeouts.

Why did you not tell me this before? :-)

We did exactly that recently for one of our ADO middle-tier database
application with persistent db connections.  All transactions were done in
stored procedure code (no nested procs) and the application performed no
explicit transaction handling and no special database exception handling.
Database errors were logged but the app moved on, oblivious to the
ramifications.

Everything was fine until a nightly job was accidentally run during the day
and that caused blocking and subsequent command timeouts.  Because the app
performed no connection cleanup after the exception, a transaction that was
in progress remained open after the timeout.  All subsequent work done on a
problem connection was done in the context the open transaction and was
never committed!

In addition to adding XACT_ABORT ON quick fix, I asked the developers to
close and re-open persistent connections following any type of database
exception.

I got the idea to try XACT_ABORT ON after perusing your error handling
articles.  Somehow, I got it in my mind that this technique was covered
there ;-)

Show quoteHide quote

Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
A really fine mess! Yes, we have experienced this in our application as
well, although that's many years behind us now. I wonder how much the
default timeout of 30 seconds have cost enterprises over the worldin
money and misery.


While better than nothing, it's not optimal, unless you already have
turned off connection pooling. Of course, if you close and reconnect
directly, and get back the same physical connection directly, everything
will be cleaned up on the spot. But if the pool gives you a different
connection, it could take 60 seconds before the rollback occurs, when
the API actually closes the connection.

In that case, I don't know that I write in my articles myself. But I
will have to add it!

Easy enough; set up a series of INSERT INTO's in a single transaction,
trap each insertion's error and do a ROLLBACK and return if you have a
failure.  Do not commit until the end of the whole thing.

The scope of transactions in T-SQL is independent of the block
structure of the language.  Think of a "transaction guy" with a bucket
of data looking at a house. The house pumps data into his bucket.   He
does not care what is happening inside; he is waiting to see a COMMIT
or ROLLBACK flag come out of the window of the house.  At that point,
he either throws the data out or throws it in the database.

But a better question why do you want to store the same data in
multiple tables?  The major reason we moved from files to RDBMS was to
get rid of redundancy -- the mantra is "one fact, one time, one way,
one place!" and not "Let's make ten copies and try to keep them all the
same!"   Instead of making ten copies of a mag tape with the same data,
we use VIEWs, CTE, and derived tables in SQL.

You did know that a row is not a record in your posting or understand
transactions,  makes me wonder if your schema is messed up because you
are mimicing files.

SQL 2005 updating data in SQL 2000 DB via linked server returns error
BUG(?): Distinct + variables TSQL
ORDER BY DATETIME field BUG?
aggregate function
search in only non tagged words