Master/Detail Transactions

From: Matt Creely (cr33d09_at_hotmail.com)
Date: 04/19/04 

Date: 19 Apr 2004 07:39:31 -0700

I have an n-Tier Intranet application. One of the pages in the site
needs to do multiple updates, deletes, inserts, all in one big
transaction. If any of the updates fails, then everything must be
rolled back. To complicate the issue, these records contain a
master/detail relationship. So, if I create the master record, say
"Order", I have to also create the detail record, call it
"OrderDetail", which uses the OrderID as it's foreign key to Order.
Also, the field "OrderID" in the master table is an identity field in
sql 2000.

So, for example, the user makes some changes to this page, and clicks
"Save". An example use case as follows:

1. Delete Order (OrderID=213) and any Order Details pertaining to it.

2. Insert New Order (OrderID=@@Identity) and create 3 OrderDetail
records, using @@Identity, and some other info.

3. Update Order (OrderID=217) with some info.

4. Delete Order (OrderID-219) and BOOM!!! FK VIOLATION OR SOMETHING

ROLL BACK ALL CHANGES!

Is this possible? Using an ado.net dataset looks like the magic
bullet. If I can create a dataset, make all of these changes, and then
attempt to run the transaction as an ADO.net SqlTransaction, that
would be very nice. I'm not sure how'd I'd do it in T-SQL, having to
pass in arrays of detail records into a stored procedure. I've done
all sorts of transaction work before, using ADO.Net (and T-SQL), but I
just don't know how to deal with the Master/Detail relationship. Does
the OrderID field need to be generated by my system? Or can I still
accomplish this with an Identity Field?

It'd be really nice to just say:

DataSet.Tables(1).Rows(y).Item("OrderID") =
DataSet.Tables(0).Rows(x).Item("OrderID")

Relevant Pages

  • RE: Master/Detail Transactions
    ... If any of the updates fails, ... pending parent and child rows within the scope of a transaction, ... cascade newly retrieved parent identity values down to pending child rows ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I absolutely think that in extremely large jobs, ... I know, I could send a single transaction containing 10,000 records but it ... > physical memory to accommodate the TWO sets of 100,000 records: ... >> The statement you make about the individual updates not hitting the ...
    (microsoft.public.data.ado)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... The statement you make about the individual updates not hitting the database ... to do with the updates being in a transaction but rather on whether you specify ... spill over and the memory starts paging out to disk that concerns me). ...
    (microsoft.public.data.ado)
  • Re: Using transactions to insert into to a table while allowing read access to existing data
    ... purpose but to roll back the transaction. ... rows with uncommitted updates are marked with locks. ... isolation level is READ COMMITTED, ... then finally committing the transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Cant use autonumber
    ... Another respondent has pointed you to his own answer. ... As for wrapping multiple updates into a single transaction: ... >> you can wrap that update into the transaction used by the bound form. ...
    (microsoft.public.access.formscoding)