Trigger-Based Event Processing
The trigger-based technique enables you to recognize changes to an entity. For this discussion, an entity is a real-world object that is represented in the database by a hierarchical set of tables. You manage the triggers using SQL*Plus or a similar tool, and you configure BizTalk Server as the listener. You do the BizTalk Server setup when you add the generated items to the BizTalk Server project—creating the event Web service and the listener.
Trigger-based event processing is a technique for listening to multiple-joined Oracle Database tables. It is also helpful for detecting when a row has been deleted or updated. Events use this technique.
The trigger-based technique provides several benefits:
It improves performance when listening to events in a group of large joined tables. For example, when it processes joined tables, Oracle creates a Cartesian product working table; when the joined tables are large, the interim working table is large.
The trigger-based technique avoids this overhead by requiring the listener to query a single small control table, and by writing to the control table only when an event actually occurs.
It increases the number of event types that the adapter recognizes. By using the trigger-based technique, you can tell when a row has been updated, deleted, or inserted.
To use the trigger-based technique, you assign a trigger to each table that you want to monitor. When a value changes, it raises the corresponding trigger, which writes data to a control table. Microsoft BizTalk Adapter for Oracle Database listens to this control table by running a query against it. When it finds a row in the control table, it reads it and returns it to the Reply_to destination using an XML document. To make sure that the row is not read again when the listener next queries the table, the listener deletes the row from the table.
Consider the following scenario:
Your application updates a row in a group of related source tables.
The update causes a row trigger to occur in the changed table. The trigger inserts a row into the control table. The new control table row includes the key value (25), the type of transaction (update), and the new cell value (orange).
The listener queries the control table and copies the new row to an XML document. It sends the document to the Reply_to destination.
The listener deletes the control table row to make sure that the row is not read again when the listener next queries the table.
The application inserts a new row into one of the source tables. The process repeats itself.
Follow these steps to implement the trigger-based event-processing technique.
Creating the Control Table
The purpose of the control table is to capture the key of each entity that has changed, regardless of which of the entity’s tables have changed. You can store a variety of information in the control table, from the key of the entity that was inserted, updated, or deleted, to the name of the table and field that was updated.
The control table’s design is a function of your application's business logic. For example, you can choose between creating one control table for a group of joined source tables, or one control table per source table. Among the issues to consider are which kinds of events to monitor (insertions, deletions, or updates), and whether you want to monitor only the highest-level table in a group of joined tables, or all the tables in the group.
Assigning Triggers to the Source Tables
The kind of change you want to monitor determines which triggers you assign, and to which tables you assign them. The triggers implement much of the event-processing logic.
For example, consider a bill of material scenario. A bill of materials is a list of all the parts required to manufacture an item, the subparts required for the parts. The complete item/parts/subparts relationship can extend to several levels, creating a data structure like a tree with the finished item as the root. In a bill of materials, where each level in the parts hierarchy is represented by a separate table, you might assign a trigger to only the highest-level table (the finished product), or you might assign triggers to all tables (the finished product and its parts and subparts).
As another example, if multiple changes are made to the same row during one listener cycle, you could configure the event adapter to record all the changes. If a row was inserted and then updated, both changes would be logged
Using the following trigger sample, you select the table PO_CDC. This is the working table or control table that is populated by the trigger and is the receiver port in BizTalk Server. The XML request depends on the structure of the table; the working table in this example contains 2 columns, WIP_ENTITY_ID and ORGANIZATION_ID.
You use the message received from this port as the starting point for your BizTalk Server orchestration. BizTalk Server receives this message from BizTalk Adapter for Oracle Database.
The following trigger occurs when a change is made to the WIP_ENTITY_NAME column in the WIP.WIP_ENTITIES table. When it occurs, it writes the relevant values to the control table PO_CDC.
CREATE OR REPLACE TRIGGER PO_CDC_WE_TRG AFTER INSERT OR DELETE OR UPDATE OF WIP_ENTITY_NAME ON WIP.WIP_ENTITIES FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO PO_CDC VALUES ( :NEW.WIP_ENTITY_ID, :NEW.ORGANIZATION_ID, 'UPDATE'); ELSE INSERT INTO PO_CDC VALUES ( :OLD.WIP_ENTITY_ID, :OLD.ORGANIZATION_ID, 'UPDATE'); END IF; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; -- Record already exists END;