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