With Intelligent Transaction functionality, a transaction not only has the capability of validating itself in its current database, but is also capable of changing its behavior (that is, the database operations) if the original behavior was invalid.
Statements of a transaction can be any SQL statements, but most often are calls to stored procedures. Statements should contain logic that is required to ensure the validity of the statement in different environments and situations.
When executing the transaction in the master database, the statements of the transaction can communicate with each other by putting transaction parameters on the Parameter Bulletin Board for the following statements of the same transaction to read. This communication ability of the statements makes it possible to create transactions that can validate themselves and adjust their behavior according to the current environment.
For example, if a transaction that is propagated to the master database attempts to add a new order to a customer whose credit limit has been exceeded, the following process takes place:
The transaction has the following operations:
▪ Insert a row to the CUST_ORDER table.
▪ Update the CREDIT column of the CUSTOMER table.
The processing of the statements goes as follows:
1 Prior to inserting a new row to the CUST_ORDER table, the INSERT_ORDER procedure checks if the customer has credit. In this example, it is assumed the credit level has been exceeded.
2 Because the customer does not have sufficient credit, the Intelligent Transaction inserts the new row to the CUST_ORDER table with a different STATUS value (for example, STATUS = 'Not approved').
3 Because the order is not a valid one, the update operation of the CREDIT column must not be done. Therefore the INSERT_ORDER procedure puts a parameter with name "ORDER_FAILED" and value "YES" to the bulletin board.
4 The UPDATE_CUST_CREDIT procedure checks the bulletin board and detects that it contains information that applies to this procedure.
5 The UPDATE_CUST_CREDIT procedure then knows that it must not update the credit amount.
6 Later, when the replica database is refreshed, the information about the customer order is updated in the replica database, to correctly indicate that the order was not processed. As a result, both the master database and replica database have correct data, even when the replica database made a request that could not be fulfilled.
