The default event generator is simple and straightforward. It makes use of a database trigger to fire events to a channel based on the changes being made in the database. But there is no customization supported in the WebLogic Integration Administration Console. There, you can specify the table to monitor along with the event/trigger type and the columns to publish. But the Administration Console doesn't have provisions to accommodate the following:
Though this cannot be achieved through the Administration Console, as we've just seen, the RDBMS Event Generator implementation is based on the database triggers to fire the events. It can be tailored to our business demands by appropriately modifying the triggers inside the database or by writing our own procedures to insert records into the shadow table. Though these aren't supported by BEA, we've been doing this for a while with great results.
As an example of using the RDBMS Event Generator, we will develop a small Inventory Management Application. The purpose behind holding the inventory is to cater to the orders immediately. This system requires building in an intelligence to order fresh stocks immediately when the quantity of stock reaches the reorder point. But the details on whether the quantity on hand has reached the reorder point can be tracked down only from persistent stores such as a database or a file system.
This scenario demands an event generator that can inform the Fresh Stock Ordering Application to procure fresh stocks whenever the item reaches the reorder point.
Here's the structure of the Inventory table:
NAME NULL? TYPE --------------------- -------- -------------- PRODUCT_ID NOT NULL VARCHAR2(10) QTY_ON_HAND NUMBER QTY_ON_ORDER NUMBER COST NUMBER(14,2)
Let's now look at how to create and use an event generator.
Here are the steps you need to follow to create the RDBMS Event Generator for the example Inventory Management Application:
Figure 1 shows some of these settings for our example.
Figure 1. Defining the channel rule for the RDBMS Event Generator (click the image for a larger version)
Here's a look at the INVENTORY_BEA_SDW table. Recall that records within this shadow table will be used by WebLogic Integration to identify the events fired from the database.
NAME NULL? TYPE ---------------------- -------- ------------- PRODUCT_ID VARCHAR2(10) OLD_PRODUCT_ID VARCHAR2(10) QTY_ON_HAND NUMBER OLD_QTY_ON_HAND NUMBER QTY_ON_ORDER NUMBER OLD_QTY_ON_ORDER NUMBER COST NUMBER(14,2) OLD_COST NUMBER(14,2) BEA_SEQ_ID NOT NULL NUMBER(28) BEA_POLLED_AT NOT NULL NUMBER(19)
Here's a look at the trigger that WebLogic Integration created on the table being monitored:
CREATE OR REPLACE TRIGGER INVENTORY_BEA_TRG AFTER UPDATE ON SYSTEM.INVENTORY FOR EACH ROW DECLARE TEMP INTEGER; BEGIN SELECT SYSTEM.INVENTORY_BEA_SEQ.NEXTVAL INTO TEMP FROM DUAL; INSERT INTO SYSTEM.INVENTORY_BEA_SDW (PRODUCT_ID, OLD_PRODUCT_ID, QTY_ON_HAND, OLD_QTY_ON_HAND, QTY_ON_ORDER, OLD_QTY_ON_ORDER, COST, OLD_COST, BEA_SEQ_ID) VALUES (:NEW.PRODUCT_ID,:OLD.PRODUCT_ID,:NEW.QTY_ON_HAND, :OLD.QTY_ON_HAND,:NEW.QTY_ON_ORDER,:OLD.QTY_ON_ORDER, :NEW.COST,:OLD.COST,TEMP); END;
Looking at the trigger, it's evident that an event will be triggered when the inventory table records are updated. But, as always, business demands more than this. For our example, let's assume that this event should be triggered only when the quantity on hand becomes less than a predefined threshold. How can this be customized to meet the business requirements?
Triggers can be easily modified provided you have some knowledge on the RDBMS. For example, business rules can be included as a part of a trigger and applied to the database. Here's a modified trigger that we used:
CREATE OR REPLACE TRIGGER INVENTORY_BEA_TRG AFTER UPDATE OF QTY_ON_HAND ON SYSTEM.INVENTORY FOR EACH ROW DECLARE TEMP INTEGER; BEGIN SELECT SYSTEM.INVENTORY_BEA_SEQ.NEXTVAL INTO TEMP FROM DUAL; IF :NEW.QTY_ON_HAND < 10 THEN INSERT INTO SYSTEM.INVENTORY_BEA_SDW (PRODUCT_ID, OLD_PRODUCT_ID, QTY_ON_HAND, OLD_QTY_ON_HAND, QTY_ON_ORDER, OLD_QTY_ON_ORDER, COST, OLD_COST,BEA_SEQ_ID) VALUES (:NEW.PRODUCT_ID,:OLD.PRODUCT_ID,: NEW.QTY_ON_HAND,:OLD.QTY_ON_HAND,:NEW.QTY_ON_ORDER, :OLD.QTY_ON_ORDER,:NEW.COST,:OLD.COST,TEMP); END IF; END;