Using INSTEAD OF triggers

First introduced in DB2 UDB V8.1, INSTEAD OF triggers are very useful to isolate applications from changes in database structure. Here's a tutorial.

This Content Component encountered an error

First introduced in DB2 UDB V8.1, INSTEAD OF triggers are very useful when you need to modify the database structure without changing the applications running against the changed database. In other words, you may use INSTEAD OF triggers and views to isolate applications from changes in database structure. This may be the only option if you are dealing with a third-party application that cannot be modified. Also you might want to avoid...

the time-consuming and tedious task of modifying several applications in many places. Let us discuss an example.

Let's suppose that currently there is a table:

CREATE TABLE CONTACT_CURRENT( 
CONTACT_ID INTEGER NOT NULL PRIMARY KEY, 
NAME VARCHAR(50))

Because the situation has changed, now there is a need to keep all the previous versions of a record every time the record is modified. Here is the new database structure:

CREATE TABLE CONTACT( 
CONTACT_ID INTEGER NOT NULL PRIMARY KEY, 
CURRENT_VERSION SMALLINT NOT NULL DEFAULT 0 )

CREATE TABLE CONTACT_DATA( 
CONTACT_ID INTEGER NOT NULL, 
VERSION_NUMBER SMALLINT NOT NULL, 
NAME VARCHAR(50), 
TIME_CHANGED TIMESTAMP DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY(CONTACT_ID, VERSION_NUMBER),
FOREIGN KEY(CONTACT_ID) REFERENCES CONTACT(CONTACT_ID) )

Our goal is to have all the existing (and unchanged) applications working against the new database structure exactly as they used to work against the old database structure. The first obvious step is to create a view mimicking the old table's structure:

CREATE VIEW CONTACT_CURRENT 
AS 
SELECT 
CONTACT.CONTACT_ID, 
CONTACT_DATA.NAME 
FROM 
CONTACT JOIN CONTACT_DATA 
ON CONTACT.CONTACT_ID = CONTACT_DATA.CONTACT_ID 
AND CONTACT.CURRENT_VERSION = CONTACT_DATA.VERSION_NUMBER

This view is good enough for SELECT statements, it substitutes for the old table seamlessly. Unfortunately, the view is not insertable, not updateable and not deleteable. Here is where INSTEAD OF triggers come very handy.

Note: I'm consistently using @ as terminating character in this tip.

This is how we make the view insertable:

CREATE TRIGGER CONTACT_CURRENT_I INSTEAD OF INSERT 
ON CONTACT_CURRENT 
REFERENCING NEW AS N 
FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
INSERT INTO CONTACT(CONTACT_ID, CURRENT_VERSION)VALUES(N.CONTACT_ID, 0); 
INSERT INTO CONTACT_DATA(CONTACT_ID, VERSION_NUMBER, NAME) 
VALUES(N.CONTACT_ID, 0, N.NAME); 
END @ 

Now, as the view is insertable, the statement below works:

INSERT INTO CONTACT_CURRENT VALUES(1, 'PETER CARSON')

CREATE TRIGGER CONTACT_CURRENT_U INSTEAD OF UPDATE 
ON CONTACT_CURRENT 
REFERENCING NEW AS N 
FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC 
UPDATE CONTACT SET CURRENT_VERSION = CURRENT_VERSION + 1 
WHERE CONTACT_ID = N.CONTACT_ID; 
INSERT INTO CONTACT_DATA(CONTACT_ID, VERSION_NUMBER, NAME) 
SELECT CONTACT.CONTACT_ID, CONTACT.CURRENT_VERSION, N.NAME FROM CONTACT WHERE CONTACT.CONTACT_ID = N.CONTACT_ID; 
END @ 

The update statement below works exactly as it would work against the old structure:

UPDATE CONTACT_CURRENT SET NAME='PETER CARSON JR.' WHERE CONTACT_ID=1

SELECT * FROM CONTACT_CURRENT 

CONTACT_ID  NAME                                               
----------- --------------------------------------------------
          1 PETER CARSON JR.             
 

The old version of the record is kept along with the new one:

               
SELECT CONTACT_ID, VERSION_NUMBER, NAME FROM CONTACT_DATA

CONTACT_ID  VERSION_NUMBER NAME                                               
----------- -------------- --------------------------------------------------
          1              0 PETER CARSON                       
          1              1 PETER CARSON JR.                                   

Similarly, the view can be made deletable:

CREATE TRIGGER CONTACT_CURRENT_D INSTEAD OF DELETE 
ON CONTACT_CURRENT 
REFERENCING OLD AS O 
FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC
DELETE FROM CONTACT_DATA WHERE CONTACT_ID = O.CONTACT_ID ;
DELETE FROM CONTACT WHERE CONTACT_ID = O.CONTACT_ID ;
END@ 
DELETE FROM CONTACT_CURRENT WHERE CONTACT_ID=1

As we have seen, there is no need to modify existing applications running against DB2 V8.1 or later. All the applications work against the new view exactly as they used to work against the old table. We have saved ourselves a lot of mundane and error-prone work. Also we have made all the changes only in one place, which is very efficient.

About the author

Alexander Kuznetsov has 15 years of experience in software design, development and database administration. Currently he improves performance of applications running in a multi-terabyte database environment. Alexander is an IBM Certified Advanced Technical Expert (DB2 Cluster) and an IBM Certified Solutions Expert (Database Administration and Application Development).

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.

This was first published in January 2004

Dig deeper on iSeries SQL commands and statements

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close