Data Management

Embed event triggers in Oracle databases and push the limits of ERP

While one benefit of an ERP system is its ability to use databases to trigger multiple events, its real power rests in embedding event triggers in the databases. Here's an overview of how this works in Oracle.


At the core of reengineering a company’s business processes for ERP is the combined power of integration and distribution. Oracle, like all ERP platforms, provides efficient and robust mechanisms for tying multiple databases together in a single transaction and triggering downline events from key causal ones. Understanding these relationships is critical for IT managers who have Oracle in their organization.

With this in mind, the real power in integration of business functions is the embedding of event triggers in databases themselves, so that any updating of a database object (whether by the user or by another automated application) triggers downline events.

Here’s an overview of this process:

A note about DML and DDL
When exploring Oracle, if you’re not yet indoctrinated, it’s important to keep in mind that Oracle has a Data Definition Language (DDL) and a Data Manipulation Language (DML). This distinction is especially critical in working with code that is database-specific rather than application-specific.

Data handling in Oracle is facilitated via SQL, through which PL/SQL is database-integrated. Oracle’s statements for application database interaction—DML statements—are executed from within PL/SQL. You can do database insertions, deletions, updates, and queries with DML statements. DDL statements may be used for the creation and maintenance of tables, outlines, and other database structures.

DDL statements, it should be noted, can’t be executed from within PL/SQL. They can, however, be executed via dynamic DDL, which refers to blocks of PL/SQL that have been constructed dynamically and compiled. (Note that you can’t execute DDL statements over a database link.)

Oracle's database triggers
A variety of database-specific triggers are now available in Oracle, covering a broad array of database activities. They include triggers for object create, alter, and drop; triggers for server errors; triggers for startup/shutdown; and triggers for logon/logoff.

These latter triggers may not seem obvious, but there is much you can do with them. For example, you can do general housekeeping or pin objects in the shared pool at startup with triggers. You can use a logon trigger to establish session context. At shutdown, you can use triggers to close out applications and to prevent table corruption. You can gather session statistics with a logoff trigger or system statistics with a shutdown trigger. Be creative.

For this article, the focus is on the initiation of downline processes when data objects are handled. When a database is modified through the creation, modification, or deletion of an object, you generally want to have some subsequent application initiated. Database triggers are your primary tool for achieving this.

Here are the most useful database events you can handle with triggers:CREATE, ALTER, DROP, ANALYZE. (There are others, for audit purposes for example, that are less commonly used.) The syntax for applying them follows this general form:
CREATE [ OR REPLACE ] TRIGGER name_of_trigger
{ BEFORE | AFTER | { event } ON { DATABASE | SCHEMA }
DECLARE
declarations
BEGIN
( procedural code; this is where your embedded process goes )
END;

An example
Here’s a situation in which you might use this. Suppose the database object you are generating or modifying is a purchase order, and you want to initiate an embedded process—say, the generation of an advance ship notice or a purchase order change notification—when such a creation or modification occurs. Your SQL would look something like this:
CREATE OR REPLACE TRIGGER purchase_order_trigger
      AFTER INSERT OR UPDATE ON purchase_order
      FOR EACH ROW
DECLARE
      ( declarations )
BEGIN
      ( block of code that initiates a transaction generating an advance ship notice
             or a purchase order change notification)
END;

The form is fairly simple, as is this example, but it should be clear that the power of this technique is considerable. There are some restrictions—autonomous transactions are not used across database links, for instance—but the design potential of these features is vast.

Commands for handling triggers
Oracle provides a set of DDL statements for trigger handling. Consider that there are times when you need a trigger and other times when you want that trigger disabled. It’s also sometimes useful to know trigger status. Here’s how you do it.

You can handle specific triggers with  ALTER TRIGGER. The SQL syntax is as follows:
ALTER TRIGGER name_of_trigger { ENABLE | DISABLE };

The DDL statements above do exactly what they seem to do; they enable and disable individual database and table triggers. But if a table or database has multiple triggers associated with it, you can handle them all at once:
ALTER TABLE name_of_table { ENABLE | DISABLE }
      ALL TRIGGERS;

Finally, to view trigger status, you can access the following data dictionary views via the queries below:
  • USER_TRIGGERS:These are all the triggers owned by the user.
  • ALL_TRIGGERS: These are all the triggers to which the user has access.
  • DBA_TRIGGERS: These are  all the triggers in the database.

You can find additional details on the syntax options for database triggers in your Oracle documentation. But the fullest use of these features requires more than the implementation of some SQL statements. Some serious design work and more than a little creativity are important ingredients in putting these features to work to integrate and expand the applications in your Oracle environment.

About Scott Robinson

Scott Robinson is a 20-year IT veteran with extensive experience in business intelligence and systems integration. An enterprise architect with a background in social psychology, he frequently consults and lectures on analytics, business intelligence...

Editor's Picks