At 03:03 AM 22/09/2007, Andre Luis wrote:

>Hi,
>
>I have the following master-detail situation:
>
>Table Sales - master
>Table Sales_Items - detail
>Using generatorLinks for both tables.
>IB_transaction := default for both tables with autoCommit = True
>
>
>Link master-detail is working fine.
>
>My doubt:
>
>qrSales.Insert;
>
>// some data is typed in appropriate controls
>
>qrSales_Items.Insert;
>
>// some data is typed in appropriate controls
>
>qrSales_Items.Post;
>
>qrSales_Items.Insert;
>
>.....
>
>qrSales.Post;
>
>Till here all fine.
>
>But, if I do a qrSales.Cancel instead of qrSales.Post, the records of
>qrSales_Items that were already posted and commited still are there.
>I'd like that when qrSales.Cancel happen, in a dssInsert state, the
>detail data being deleted.
>
>I already have a before delete trigger on table Sales, that delete
>Sales_Items accordly.
>
>Lookin for "Master-Detail" at IBO Online Faq the first topic I found is:
>
>I see that with master-detail relations I get a post for each detail
>insert. Is this normal? Isn't it too many posts and too much traffic?
>
> When using master-detail (and not cached updates) if you post a
>detail record that has been inserted, it will result in an INSERT
>statement being sent to the server. If the master record was also in
>insert state it will do a post retaining so that referential integrity
>will be satisfied. Thus, it is possible for two INSERT statements to
>be executed on the server.
>
> Then, when the master record is posted, it now performs an UPDATE
>on the server instead of an INSERT, because the record is already on
>the server due to the post retain. Should it have been cancelled, a
>DELETE would have been performed on the server. In this case, the
>detail records should also have been deleted via cascading referential
>integrity or a trigger, which are assumed to be in place.
>
>
>So, what am I doing wrong? Should qrSales and qrSales_Items be under
>the same ib_transaction or what?

If you want the ability to cancel an operation, you should never use
Autocommit=True. This is especially important with Master-detail.

Post and Commit are two separate steps of a DML change: Post writes
a new record version to the database, Commit makes it permanent (and
therefore visible to other transactions). When you use Autocommit,
each part of the operation gets both posted AND committed in one
step. Autocommit causes the transaction to end and silently starts a
new transaction using the same resources.

Always wrap a Master-detail operation inside a single, explicit
transaction that begins with a StartTransaction and ends with a Commit.

Helen