How to: |
Trigger-based event processing is a technique for listening to multiple joined relational tables.You also can use it to detect when a row was deleted or updated.
The trigger-based technique provides the following benefits:
When processing joined tables, the database system creates a Cartesian product working table. When the joined tables are large, the interim working table is very large. The standard technique of processing database events, in which the adapter periodically listens to the entire structure of joined tables, can consume a significant amount of computing resources.
The trigger-based technique avoids this overhead by requiring the Table Listener to query a single small control table and by writing to the control table only when an event actually occurs.
Using the trigger-based technique, you can tell when a row was updated, deleted, or inserted. Using the standard technique, you can tell only when a row was inserted.
To use the trigger-based technique, you assign a trigger to each table that you want to monitor. When a value changes, it fires the corresponding trigger that writes data to a control table. The iWay Technology Adapter for RDBMS listens to the 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 port disposition created when the port is configured via an XML document. To ensure the row is not read again when the listener next queries the table, the listener deletes the row from the table.
The trigger-based technique enables you to recognize changes to an entity. For the purposes of 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 a native RDBMS tool (such as SQL*Plus for Oracle tables) and configure the listener using the iWay Web Console.
Note: Event processing may be limited for some non-relational databases due to the functionality of the database and its interaction with the iWay server component. For more information on the iWay server component, see the iWay Data Adapter Administrator User's Guide or consult with your DBA.
The following figures illustrate trigger-based event processing:
The update causes a row trigger to fire in the changed table as shown in the following figure.
The process repeats itself.
To implement the trigger-based event processing technique:
The purpose of the control table is to capture the key of each entity that changed, regardless of which entity table changed.
You can store a variety of information in the control table, including the key of the entity that was inserted, updated, or deleted and the name of the table and field that was updated.
The design of the control table is a function of the business logic of your application. 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 the 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 of the tables in the group.
The triggers you assign, and to which tables you assign them, is determined by what kind of change you want to monitor. The triggers implement event-processing logic. For a sample trigger, see Trigger on WIP_ENTITY_NAME Column in an Oracle Table.
For example, consider a bill of materials scenario. (A bill of materials is a list of all the parts required to manufacture an item, the subparts required for the parts, and so on. 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, 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).
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 are logged.
In addition to the required listener properties, for trigger-based event processing you also must provide values for the following optional properties:
SQL Query: the SQL SELECT statement that identifies the control table to which the adapter listens and with which it queries the table to determine changes in the source tables.
Post Query: the query that identifies the rows that the adapter automatically deletes from the control table.
For detailed instructions about configuring a listener, see How to Create a Channel. For information about Post query operators, see The Post-query Parameter Operators.
The following trigger fires when a change is made to the WIP_ENTITY_NAME column of the WIP.WIP_ENTITIES Oracle E-Business Suite table. When it fires, the trigger writes the relevant values to the control table IWAY.IWAY_PO_CDC.
CREATE OR REPLACE TRIGGER IWAY.IWAY_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 IWAY.IWAY_PO_CDC VALUES ( :NEW.WIP_ENTITY_ID, :NEW.ORGANIZATION_ID, 'UPDATE'); ELSE INSERT INTO IWAY.IWAY_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;
iWay Software |