Trigger-based Event Processing

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:

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:

  1. Your application updates a row in a group of related source tables as shown in the previous figure.

    The update causes a row trigger to fire in the changed table as shown in the following figure.

  2. The trigger inserts a row into the control table, and the new control table row includes the key value (25), the type of transaction (update), and the new cell value (orange) as shown in the previous figure.
  3. The listener queries the control table and copies the new row to an XML document. It sends the document to the Reply_to destination as shown in the following figure.

  4. The listener deletes the control table row to ensure that the row is not read again when the listener next queries the table as shown in the following figure.

  5. The application inserts a new row into one of the source tables as shown in the following figure.

The process repeats itself.


Top of page

x
Procedure: How to Implement Trigger-based Event Processing

To implement the trigger-based event processing technique:

  1. Create the control table.

    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.

  2. Assign triggers to the source tables.

    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.

  3. Configure the listener when creating a channel in the iWay Explorer console.

    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.



Example: Trigger on WIP_ENTITY_NAME Column in an Oracle Table

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