Monday, April 27, 2009

Applying updates for master/detail tables

When you apply updates for master/detail tables, the order in which you list datasets to update is significant. Generally you should always update master tables before detail tables, except when handling deleted records. In complex master/detail relationships where the detail table for one relationship is the master table for another detail table, the same rule applies.
You can update master/detail tables at the database or dataset component levels. For purposes of control (and of creating explicitly self-documented code), you should apply updates at the dataset level. The following example illustrates how you should code cached updates to two tables, Master and Detail, involved in a master/detail relationship:

Database1->StartTransaction();

try
{
Master->ApplyUpdates();
Detail->ApplyUpdates();
Database1->Commit();
}
catch(...)
{
Database1->Rollback();
throw;
}
Master->CommitUpdates();
Detail->CommitUpdates();

If an error occurs during the application of updates, this code also leaves both the cache and the underlying data in the database tables in the same state they were in before the calls to ApplyUpdates.
If an exception is thrown during the call to Master->ApplyUpdates, it is handled like the single dataset case previously described. Suppose, however, that the call to Master->ApplyUpdates succeeds, and the subsequent call to Detail->ApplyUpdates fails. In this case, the changes are already applied to the master table. Because all data is updated inside a database transaction, however, even the changes to the master table are rolled back when Database1->Rollback is called in the catch block. Furthermore, Master->CommitUpdates is not called because the exception which is rethrown causes that code to be skipped, so the cache is also left in the state it was before the attempt to update.

To appreciate the value of the two-phase update process, assume for a moment that ApplyUpdates is a single-phase process which updates the data and the cache. If this were the case, and if there were an error while applying the updates to the Detail table, then there would be no way to restore both the data and the cache to their original states. Even though the call to Database1->Rollback would restore the database, there would be no way to restore the cache.

No comments:

Post a Comment