You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > DEVELOPER > GENERAL

INSTEAD OF Triggers Provide Additional Possibilities for DB2 Developers


 

There's an increasing trend toward driving more application logic into the database. Application programmers want to collaborate with the database for such purposes as providing data security and code portability in their applications. Programmers want to simplify their application development and centralize functions that are applicable to the data, regardless of where and how the data is accessed. DB2* Universal Database* (UDB) for iSeries* was recently enhanced to support SQL INSTEAD OF triggers. INSTEAD OF triggers introduce a new kind of synergy between applications and DB2 UDB for iSeries.

 

Applications frequently use SQL views for read access to an underlying table. For all but the simplest views, however, the application must access the base table directly when inserting, updating or deleting data. The programmer must deal with separate targets for reads and writes. INSTEAD OF triggers provide a mechanism for applications to unify the target, both reading and writing to views, regardless of the views complexity.

 

INSTEAD OF triggers provide a mechanism for programmers to define specific actions to be taken when writing to a view. Previously, when attempting to write to a read-only view, the DB2 engine would fail the operation. But "instead of" failing the operation, an INSTEAD OF trigger can intercept data and direct it to an underlying table, redirect it to another table, transform it or even ignore it-the possibilities are endless.

 

Triggers at a Glance

A trigger is a set of actions executed automatically whenever a specified event occurs to a specified file. The set of actions can include almost any operation allowed on the system. The event can be an insert, update or delete operation. When such an event is executed, the trigger is said to be activated. The trigger can be run either before or after the event. Triggers have many uses including enforcing security or business rules, or enforcing data integrity when used along with constraints.

 

DB2 UDB for iSeries supports two breeds of triggers:

 

 

  • External triggers-These triggers are created using the Add Physical File Trigger (ADDPFTRG) command. 

     

     

  • SQL triggers-These triggers are created using the SQL CREATE TRIGGER statement. 

     

     

    An INSTEAD OF trigger is a special type of SQL trigger.

     

    One difference between INSTEAD OF triggers and other trigger types is that other triggers are executed before or after the trigger event. INSTEAD OF triggers are literally processed instead of the insert, update or delete operation that activates the trigger. The other significant difference is that BEFORE and AFTER triggers can only be defined on SQL tables and physical files. INSTEAD OF triggers are defined on SQL views.

     

    For each view, one INSTEAD OF trigger may be defined per operation. INSTEAD OF triggers may coexist with other types of triggers in a file hierarchy. Normally, views inherit triggers from their based-on tables. For example, if an AFTER UPDATE trigger is created on a table, an update operation performed on a dependent view (i.e., a view created over the table) will activate the trigger. An INSTEAD OF trigger for a given operation, however, will preclude any trigger on the based-on table for that same operation. That means if an INSTEAD OF UPDATE trigger is created for the dependent view, an update operation performed on the view will activate the INSTEAD OF trigger and will no longer activate the underlying AFTER trigger.

     

 

 

"Instead of" failing the operation, an INSTEAD OF trigger can intercept data and direct it to an underlying table, redirect it to another table, transform it or even ignore it--the possibilities are endless.

Kathryn Steinbrink is an advisory software engineer in the iSeries Database organization for IBM. Kathryn can be reached at krs@us.ibm.com.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Are You Multilingual?

Rational enables development in multiplatform environments

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters