Databases Reference
In-Depth Information
a query window in SQL Server Management Studio, for example. If you close a
connection that contains open transactions, those transactions are rolled back.
10.1.5 Recognizing and Resolving Potential Problems
We've already discussed one potential problem with transactions, an error
occurring during the transaction so that only some of the statements are exe-
cuted. This can lead to inconsistent, incorrect, and invalid data if you commit
the transaction at that point. Another potential problem is that it's possible that
a transaction is just poorly written and attempts an illegal or inappropriate
action. Integrity functions in the database management system work to preserve
the integrity of the database by performing the following actions:
▲
Monitor and scrutinize all transactions, especially those with database
updates.
▲
Detect any data integrity violations by transactions.
▲
If a violation is detected, take appropriate action such as:
▲
Rejection of the operation.
▲
Reporting of the violation.
▲
Correction of error condition, if possible.
While it would be convenient if the DBMS could handle all of these func-
tions by itself and completely in the background, this isn't the case. Management
systems include various tools and implement functionality to assist, but some of
the logic involved needs to be built into the application. Consistency and integrity
are insured by having the management system and application work together.
Consider a situation where you have an application that automatically
processes customer orders. It uses a transaction to update the inventory quanti-
ties, generate an order, and update the customer's outstanding balance. The trans-
action creates an order in the OrderHead and OrderTail tables. Let's add some
business rule conditions, as follows:
▲
Each order must have a unique order number.
▲
Each detail item must be related to a valid order.
▲
Each order must have at least one detail item.
Okay, so how do you handle these? Let's take the last item first, because it's
potentially the trickiest one of the set. This isn't something that the database can
easily check through table constraints, so it's an issue better resolved by the appli-
cation. The application needs to verify that there is at least one valid detail item
before committing the transaction. An even better solution would be using that
as a decision point for whether or not it even starts the transaction. If you never
start it, then you don't have to roll it back because the order doesn't qualify.
