Office applications Toolkit

Oracle database event triggering

Tags:
ERP,
Database,
Db,
Distributed

Scott Robinson Tech Republic

Published: 26 Feb 2003 14:55 GMT

Many in the IT world don't automatically think Oracle when they think ERP, though they well might, for Oracle is a formidable platform. Oracle's history is richer than its role as an ERP player. Its long tradition as a powerful and robust database platform, along with its cutting-edge technological evolution in high-volume efficiency, often tend to overshadow the fact that it is second only to SAP among ERP players.

Perhaps it is its historical emphasis on database engineering, and the consequential subtlety of its application distribution mechanisms, that make integration and distribution of Oracle-based applications a bit intimidating to consultants, but they need not be.

First in a series
This is the first article in a series that examines the role of Oracle databases in ERP.

Sending orders downline
The primary (but certainly not the only) enabling mechanisms for application integration and distribution are the embedded event trigger, and its pinnacle, the database event trigger. The first of these may be summarised as follows:

When an application user initiates a business process, the system automatically initiates subsequent processes with no further human intervention.

Consider this example: A user sits down at a computer and receives a telephone call from a customer ordering a product. The user interfaces with the relevant database directly in the course of the phone call, creating an order in the system. The database interface the user is utilising contains embedded event triggers that initiate several downline processes: a warehouse database query is made; the "zero" response reroutes the query to a remote warehouse, and a delivery order is generated; an advance ship notice is generated and routed to the customer; inventory is modified; and an instruction is sent to the accounting database, which generates an invoice that is forwarded to the customer.

The second (and more powerful) mechanism may be characterised this way:

When data in a database object is changed (or, by extension, when an object is created or deleted within a database), the system automatically initiates subsequent downline processes.

The order/invoice loop was already automated in the form of EDI and in place years before ERP penetrated the United States. (I implemented dozens of these before I saw my first ERP database event trigger at work (in an SAP system rather than Oracle). Basically, the same events occur, but instead of the event trigger being embedded in the user's database interface, it's embedded in the database itself. An order would come in via EDI (or directly from a user interface), and the action of creating a new order in the database would set in motion the flurry of activity above (and similar chains of events occurred with the modifying or deleting of an order, whether by user or by EDI).

It is not an overstatement to say that these powerful concepts turn inanimate systems into living ones. But you need a sense of how to implement such enhancements in order to design an integrated system with the desired functionality.

Oracle's integration and distribution features
The database link feature is Oracle's front-line facility for enabling distributed applications. It allows you to create a single transaction that will update multiple databases (as in the example above). The database link is an object that specifies how you want such updates to occur, whenever the initiating event (usually the first update in the chain) happens. (Note: Setting up the database link itself is a process with many situational variables; refer to your manuals for this). Within such a link, you can easily set up remote database access, as follows (where X is an order table in a remote database and inventory_database is defined in the database link):

select * from X*inventory_database;

You can then use create synonym to make this selected remote a local table to your application. Oracle handles the rest.

You can now use update, insert, delete, and any other table-handling command with this table (by addressing its synonym) in your transaction, without having to make any additional provisions. In effect, the remote database is now local to your transaction. And you can do this with as many remote databases as you like, via database links.

But this only ties things together. You must also create autonomous transactions and triggers for distributed and integrated applications.

Go to section: