INSTEAD OF Triggers - All Views are Updatable!

Views are commonly used to separate the logical DB2 schema from the physical schema. This article assesses which views are updatable on their own, and then introduces a feature new with DB2 UDB V8.1 called INSTEAD OF triggers which makes all views updatable.

Share:

Serge Rielau, Technical Manager for the SQL Query Compiler , IBM Toronto Lab

Serge Rielau started his life in DB2 six years ago through a year-long internship at the Almaden Research Center where he worked on inline SQL PL and structured types. Since then he has been responsible for various SQL enhancements, including typed views and generated columns. As a technical manager for the SQL Query Compiler he now drives language changes into DB2 as well as the implementation of the SQL standard. He can be reached through comp.databases.ibm-db2 or at srielau@ca.ibm.com.



10 October 2002

Introduction

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable. This article assesses which views are updatable on their own and then introduces INSTEAD OF triggers, a feature new with DB2® Universal DatabaseTM V8.1 for Linux, UNIX®, and Windows® that makes all views updatable.


Prerequisite

Many examples in this article use the SQL Procedural Language (SQL PL). Since SQL PL uses the semicolon (;) in its body to separate individual statements, you must use an alternate delimiter for CLP or the Command Center. Throughout this paper we will use the dollar sign ($) to delimit statements.

To start a CLP session for working with the examples in this paper, type: db2 -td$ from your shell. For the Command Center, set and activate the delimiter by clicking on Tools -> ToolsSettings -> Use statement termination character.


A tour through updatable views

Before we can take you on a tour through updatable views, we need to get some basic understanding of what "updatable" means and what the basic problems are that DB2 (or any database management system, for that matter) has to solve to update rows in the database through a view. To begin with, by default all the information DB2 has about a view is its definition, namely the query that specifies the table derived by the view.

To be precise we need to distinguish between three levels of updatability:

Deletable: In order for DB2 to delete a row from a view it must be able to map the row specified in the view to one, and only one, row in a base table.

Updatable : In order to update a column in a view, DB2 must not only be able to map the row specified in the view to one row in a base table, it also must be able to map the column to be updated to a column in a base table. Hence all views that are updatable must by definition also be deletable.

Insertable: In order for a row to be inserted into a view, DB2 must be able to map the new row to a table and to map all the columns specified to columns in that table. Thus all views that are insertable are by definition updatable and hence deletable.

Let's now define a few tables and then investigate the updatability of various views of those tables:

CREATE TABLE T1(c1 INT, c2 FLOAT)$ 
INSERT INTO T1 VALUES (5, 6.0),  
                      (6, 7.0),  
                      (5, 6.0)$ 
CREATE TABLE T2(c1 INT, c2 FLOAT)$ 
INSERT INTO T2 VALUES (5, 9.0), 
                      (5, 4.0), 
                      (7, 5.0)$

For all examples in this article, let's assume that the data as below is present. The examples shall not influence each other. You can do this by turning auto commit OFF using the +c option in the CLP, or from the Command Center deactivate Options -> Execution -> Automatically Commit. Use the ROLLBACK command after each example.

Example 1:

CREATE VIEW V1(c1)  
AS SELECT c1 FROM T1 WHERE c2 > 0$

This is a very simple view. The derived table contains a subset of the rows and a subset of the columns of T1. This view is deletable since DB2 can keep track of the origin of each of the rows and delete the respective row in the base table.

DELETE FROM V1 WHERE c1 = 6$

Note, however, that if one performed a positioned delete and deleted one row where V1.c1 equals 5, there is no telling which of the two rows where T1.c1 equals 5 will be deleted.

The view is also updatable:

UPDATE V1 SET c1 = c1 + 5 WHERE c1 = 5$

V1.c1 can be directly mapped to T1.c1.

What about INSERT? Clearly any row inserted into V1 should be inserted into T1. The value specified for V1.c1 should be used for T1.c1. But what about T1.c2? Which values will be used? After all, the view does not know of this column and thus the user could not specify a value for it.

INSERT INTO V1 VALUES (8)$

Here a definition is required, and the SQL Standard has provided it: Each column value that is not provided is initialized to the implicit or explicit default of the column. Since no explicit default was specified for T1.c2, the value NULL is chosen and (8, NULL) is inserted into T1.

Example 2:

What happens when there is an expression?

CREATE VIEW V2(c1, c2) 
AS SELECT c1, c2 * c2 FROM T1$

Compared to the first example, nothing has changed with respect to deletablity. DB2 still knows which row in the view was produced by which row in the base table. The V2.c1 column is updatable and therefore the view is updatable.

However, V2.c2 is not updatable. The reason is that there is no way to decide the value of T1.c2 from any given V2.c2. DB2 could try to use the inverse operation of the multiplication to find matching values; however, even this simple example makes clear that such mathematical approaches can only work in the most trivial cases. The mere attempt to explain the capabilities and limits of such an algorithm shows its lack of usability.

What does all this mean to insertability? Prior to DB2 V8, a view was required to have all columns updatable in order to be insertable. In DB2 V8 it is sufficient to have just one updatable column. As long as the other columns are not specified they will be ignored.

INSERT INTO V2(c1) VALUES (7)$

The system will insert (7, NULL) into T1. Note that the view would be deletable even if no column had been updatable. By the same reason the only argument against inserting into a view with no updatable columns is that neither VALUES nor SELECT is defined without a single column.

Example 3:

CREATE VIEW V3(c1, c2, c3) 
AS SELECT T1.c1, T1.c2, T2.c2  
     FROM T1, T2 WHERE T1.c1 = T2.c1$

This view is derived from a join. In this case its result is:

SELECT * FROM V3 ORDER BY c1, c2, c3$ 
 
C1  C2    C3 
--  ---  --- 
 5  6.0  4.0 
 5  6.0  4.0 
 5  6.0  9.0 
 5  6.0  9.0

This view is not deletable. While each row in the view can be traced back to one row in each of the tables T1 and T2, deleting the first row (5, 6.0, 4.0) by deleting the respective rows in T1 and T2 would also, indirectly, delete the second (5, 6.0, 4.0) and one of the (5, 6.0, 9.0) rows. This behavior is not intuitively clear, in particular for a user who doesn't know the view's query.

There are cases in which a deleted row in the view results in one row deleted in the base table without having an undesired impact on the view. This would be the case if, for example, both T1.c1 and T2.c1 were unique. DB2 today does not consider this special case.

Since V3 is not deletable it is also not updatable. Again, strange things would happen if you attempted to update one row in the view, or even worse, update V1.c1 to different values for different rows in the view.

Similarly the semantics of INSERT is not clear. Adding another row (5, 6.0, 9.0) could have varying outcomes, depending on the semantics chosen for the base table inserts. Does this mean that no updatable view can refer to another table? Not so. As discussed in Example 2, views with expressions are very well updatable. So a scalar subquery is perfectly in order.

Example 4:

CREATE VIEW V4(c1, c2) 
AS SELECT c1, c2 FROM T1 
   UNION ALL 
   SELECT c1, c2 FROM T2$

Every row in V4 clearly originated from one row in a specific table. For that reason a view based on UNION ALL is deletable. If a column is not based on an expression, then the column is also updatable. However, the view is not insertable for the obvious reason: it cannot be determined into which base table any given row should be inserted. Allowing a row into both base tables is not satisfactory because a subsequent select from the view would show the row twice. To allow INSERT through UNION ALL, constraints are required on the base tables that dispatch any given row to exactly one table.

See the article "Updatable UNION ALL Views in DB2 Universal Database Version 8" (developerWorks, September 2002) for more details on INSERT through UNION ALL views.

There are some restrictions to views on UNION ALL that should be noted. A column of a UNION ALL view is not updatable even if DB2 itself injected an expression.

CREATE VIEW V5(c1, c2) 
AS SELECT c2, c1 FROM T1 
   UNION ALL 
   SELECT c1, c2 FROM T2$

DB2 had to cast both T2.c1 and T1.c1 to FLOAT. Both V5.c1 and V5.c2 are now based on expressions and thus not updatable. Nonetheless, V5 is still deletable.

CREATE VIEW V6(c1, c2) 
AS SELECT c1, c2 FROM T1 
   UNION ALL 
   SELECT c1, c2 FROM T1$

A view like V6 is also called a "diamond" because the processing fans out from a single source into two operations (SELECT) and then comes back together again (UNION ALL). Diamonds are read-only. The rows cannot even be deleted. The reason is that each row from T1 is represented twice in V6. So it is not possible to delete just one row in V6. Also it is not possible to update one row only.

The reverse problem arises when a UNION (or DISTINCT) is used. Now each row in the view can be mapped to potentially many rows in the base table. Should only one row or all matching rows in the base table be deleted?

We have discussed the idea behind the updatability of a few different kinds of views. There are other views that are not updatable; however, let's move on to the big equalizer of view updatability: INSTEAD OF triggers.


INSTEAD OF triggers

A view is defined by its body. This is very obvious for SELECT queries. For UPDATE, DELETE and INSERT we discussed above that, while for some classes it is possible to make assumptions on how the view defines obvious semantics, for most cases it does not. This is where INSTEAD OF triggers jump into the breach. An INSTEAD OF trigger prevents DB2 from trying to interpret the view definition for the update operation. "Instead of" doing that, it will execute the body of the trigger, relying on the definer to come up with meaningful semantics.

For example:

CREATE VIEW V7(c1, c2) 
AS SELECT DISTINCT c1, c2 FROM T1$

An INSTEAD OF trigger can be defined to delete all rows in T1 matching a given row in V10 or to delete only one according to some predetermined rule.

Let's define a trigger that deletes all matching rows in the base table:

CREATE TRIGGER V7_DELETE INSTEAD OF DELETE ON V7 
  REFERENCING OLD_TABLE AS OLD_TAB 
  OLD AS O FOR EACH ROW MODE DB2SQL 
  DELETE FROM T1 WHERE o.c1 = c1 AND o.c2 = c2$

Superficially, only two clauses have changed compared to a normal trigger. First we defined the keywords INSTEAD OF. Second we specified the name of a view rather than a base table.

INSTEAD OF is a very clear clause. It does not mean execute the trigger before attempting the delete. It doesn't mean do it after. It literally means, forget about the delete. Execute this piece of code instead.

INSTEAD OF triggers are always created for views, never for base tables or nicknames. INSTEAD OF triggers in DB2 V8 are always FOR EACH ROW triggers. This means the trigger is executed once for each row that qualifies for the delete, update or insert operation against the view.

The concept of transition tables and transition variables holds just the same as for normal triggers. Table 1, below, shows where the transition variables and tables can be used, depending on the type of INSTEAD OF trigger:

Table 1. Transition variables

OLDOLD_TABLENEWNEW_TABLE
INSERTNONOYESYES
UPDATEYESYESYESYES
DELETEYESYESNONO

OLD_TABLE and NEW_TABLE transition tables specify the set of rows being processed by the view. OLD and NEW transition variables are the specific rows in the respective transition tables processed by the current invocation of the trigger.

In a DELETE or UPDATE trigger the OLD_TABLE is the set of rows that qualified for the delete. This means they are the rows as a cursor over the view sees them. Here are the values for OLD_TABLE in our example:

OLD_TAB: 
 C1   C2 
--- ---- 
  5  6.0 
  6  7.0

The order in which rows are processed by the trigger is arbitrary. This means there is no rule whether the above trigger will see (5, 6.0) or (6, 7.0) first as the OLD transition row. It is further important to note that for all triggers, including INSTEAD OF triggers, the transition tables are completely determined before the trigger is ever fired. This means we can delete from T1 without seeing changes in OLD_TABLE.

Note that the trigger above cannot have a WHEN clause in the triggered action. INSTEAD OF triggers fire unconditionally. The same is true for column lists typical for BEFORE and AFTER UPDATE triggers. These, too, are a kind of trigger condition and are not allowed for INSTEAD OF triggers.

CREATE TRIGGER V7_UPDATE INSTEAD OF UPDATE ON V7 
  REFERENCING NEW AS n OLD AS o  
  FOR EACH ROW MODE DB2SQL 
  UPDATE T1 SET (c1, c2) = (n.c1, n.c2) 
   WHERE c1 = o.c1 AND c2 = o.c2$

The trigger will fire no matter which column is updated.

Diagram 1 shows the complete syntax diagram for INSTEAD OF triggers:

Diagram 1. Syntax diagram for INSTEAD OF triggers

>>-CREATE TRIGGER--trigger-name--INSTEAD OF--+-INSERT-+--ON--view-name-------> 
                                             +-DELETE-+ 
                                             '-UPDATE-' 
  
>-----+---------------------------------------------------------------------+> 
      |              .----------------------------------.                   | 
      |              V       .-AS-.                     |                   | 
      '-REFERENCING---+-OLD--+----+--correlation-name-+-+-+---------------+-' 
                      |     .-AS-.                    |    
                      +-NEW-+----+--correlation-name--+ 
                      |           .-AS-.              | 
                      +-OLD_TABLE-+----+--identifier--+ 
                      |           .-AS-.              | 
                      '-NEW_TABLE-+----+--identifier--' 
  
>----FOR EACH ROW---MODE DB2SQL----SQL-procedure-statement------------------><

Note: The DB2 V8.1 documentation shows the need for a DEFAULTS NULL clause. This clause has been removed for V8.1. See the Exception table example below for the behavior of defaults in the context of INSTEAD OF triggers.

The SQL-procedure-statement can be either an SQL statement such as INSERT, UPDATE, DELETE, SELECT or VALUES, or a dynamic compound statement using BEGIN ATOMIC ... END.


Learning by example

But enough of the theory. Let's go through some examples that you just might come across in the real world.

The garbage bin

INSTEAD OF triggers can be as good as you make them and as bad as you allow them to be. The only connection between the definition of a view and the INSTEAD OF trigger is the row definition (signature) of the transition tables that match the column types and names as inherited from the views query.

What does this mean? If you define an INSTEAD OF trigger you can do whatever you please.

CREATE VIEW GARBAGE(text) 
AS SELECT CAST(NULL AS CLOB(20M))  
     FROM SYSIBM.SYSDUMMY1  
     WHERE 1 = 0$ 
      
CREATE TRIGGER INSERT_GARBAGE 
INSTEAD OF INSERT ON GARBAGE FOR EACH ROW MODE DB2SQL 
BEGIN ATOMIC  
END$ 
 
INSERT INTO GARBAGE VALUES 'unrecoverable document'$

Well, we got ourselves a nice trash bin here. For this reason only users with CONTROL privileges on the view, the view definer, the SYSADM, or the DBADM are allowed to create an INSTEAD OF trigger on the view.

Transparent encryption

After this most trivial of triggers, lets move on to a more common variety, namely using INSTEAD OF triggers to supply the "inverse" operation for an encryption expression in a view.

CREATE TABLE USERS  
   (user VARCHAR(20), 
    system VARCHAR(30), 
    login VARCHAR(20),  
    password VARCHAR(40) FOR BIT DATA)$

The table above holds IDs and encrypted passwords for users on different systems. The following view decrypts the rows associated with the current user given the user supplies the correct credentials.

CREATE VIEW MY_LOGINS(system, login, password) 
AS SELECT system, login, decrypt_char(password) 
     FROM USERS AS u WHERE u.user = USER$

In order to update or insert through this view we need to define INSTEAD OF triggers which encrypt the user-provided passwords for storage in the base table.

CREATE TRIGGER INSERT_MY_LOGINS INSTEAD OF INSERT 
  ON MY_LOGINS REFERENCING NEW AS n  
  FOR EACH ROW MODE DB2SQL 
  INSERT INTO USERS 
    VALUES(USER, n.system, n.login, 
           encrypt(password))$ 
 
CREATE TRIGGER UPDATE_MY_LOGINS INSTEAD OF UPDATE 
  ON MY_LOGINS REFERENCING OLD AS o NEW AS n  
  FOR EACH ROW MODE DB2SQL 
  UPDATE USERS U 
    SET system = n.system, 
        login = n.login, 
        password = encrypt(n.password) 
    WHERE system = o.system 
      AND login = o.login 
      AND U.user = USER$

Encrypted data is protected through the ENCRYPTION PASSWORD special register.

SET ENCRYPTION PASSWORD = 'unbreakable'$

Now we fill in the table with our confidential information:

INSERT INTO MY_LOGINS  
  VALUES('AFS',   'srielau', 'mydogsname'), 
        ('Linux', 'root',    'oopsIforgot'), 
        ('IIUG',  'Rielau',  '123456789')$ 
 
SELECT * FROM MY_LOGINS WHERE system = 'Linux'$ 
 
SYSTEM  LOGIN     PASSWORD 
------  -------   ------------ 
Linux   root      oopsIforgot

On the base table the password is encrypted. No one, not even DB2 support, can get it back without the password set above.

SELECT * FROM USERS U ORDER BY U.user, system, login$ 
 
USER     SYSTEM   LOGIN     PASSWORD
-------- -------  -------   ------------- 
SRIELAU  AFS      srielau   0x........... 
SRIELAU  IIUG     Rielau    0x........... 
SRIELAU  Linux    root      0x...........

It is possible to update the password or any other field in the view.

UPDATE MY_LOGINS  
  SET password = 'mycatsname' 
  WHERE system = 'AFS' AND login = 'srielau'$

The view, however, was deletable in the first place. So we can delete entries without an INSTEAD OF DELETE trigger.

DELETE FROM my_logins WHERE SYSTEM = 'AFS'$

For more details on encryption refer to the article "Encrypting Data Values in DB2 Universal Database" (developerWorks, August 2001).

Let's crank up the complexity a notch with a more sophisticated security example.

Group- and row-level security

A manager can view the salaries of his employees, but not those of the employees of his peer managers, or his peer manager's salary. The second-line manager can see the salary of her direct as well as indirect reports.

Let's model such a schema and then define INSTEAD OF triggers that allow managers to update employee information of their charges.

CREATE TABLE PROFILES 
(empid INT, name VARCHAR(20), sqlid VARCHAR(18), 
 mgrid INT, salary DECIMAL(9,2), ismgr CHAR(1))$

We want to seed some data, in this case a four-level hierarchy. We are a second-line manager named 'MySelf.'

INSERT INTO PROFILES 
VALUES(0001, 'SuperBoss',  'sboss', NULL, 500000, 'Y'), 
      (1001, 'BigBoss',    'bboss', 0001, 200000, 'Y'), 
      (1002, 'MySelf',     USER,    0001, 250000, 'Y'), 
      (2001, 'FirstLine',  'fline', 1001, 100000, 'Y'), 
      (2002, 'MiddleMen',  'mmen',  1001, 110000, 'Y'), 
      (2003, 'Yeti',       'yeti',  1002, 90000,  'Y'), 
      (2004, 'BigFoot',    'bfoot', 1002, 80000,  'N'), 
      (3001, 'TinyToon',   'ttoon', 2001, 50000,  'N'), 
      (3002, 'Mouse',      'Mouse', 2001, 40000,  'N'), 
      (3003, 'Whatsisname','wname', 2002, 45000,  'N'), 
      (3004, 'Hasnoclue',  'hclue', 2002, 38000,  'N'), 
      (3005, 'Doesallwork','dwork', 2003, 15000,  'N')$

A recursive view allows us to determine all our employees' salaries, including our own.

CREATE VIEW my_emps(empid, level, salary) 
AS WITH rec(empid, level, salary)  
         AS (SELECT empid, 0, salary FROM PROFILES 
               WHERE sqlid = USER 
             UNION ALL 
             SELECT P.empid, level-1, P.salary  
               FROM PROFILES P, REC R 
               WHERE level > -100 
                 AND R.EMPID = P.MGRID) 
   SELECT empid, level, salary FROM rec$

The view below will be the interface used by anyone reading employee information. We consider the employee id(empid), the employee name(name), their manager's name(name), their SQLID(sqlid) and whether they are managers or not(ismgr), public information. Employees can only see their own salaries and those of their subordinates.

CREATE VIEW PROFILES_V(empid, name, mgrname,  
                       salary, sqlid, ismgr) 
  AS SELECT P.empid, P.name, 
            (SELECT name FROM PROFILES M  
              WHERE M.empid = P.mgrid), 
            ME.salary, P.sqlid, P.ismgr 
     FROM PROFILES P LEFT OUTER JOIN MY_EMPS ME  
       ON ME.empid = P.empid$

From our analysis earlier we know that this view is not deletable and therefore not updatable or insertable. In order to keep encapsulation, we need to define a full set of INSTEAD OF triggers:

CREATE TRIGGER INSERT_PROFILES_V  
  INSTEAD OF INSERT ON PROFILES_V REFERENCING NEW AS n 
  FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    DECLARE mgrid INT; 
    DECLARE ismgr CHAR(1); 
    SET (mgrid, ismgr)  
      = (SELECT empid, ismgr FROM PROFILES  
          WHERE name = n.mgrname); 
    IF mgrid NOT IN (SELECT empid FROM my_emps) 
       OR ismgr = 'N' 
    THEN 
      SIGNAL SQLSTATE '70000'  
         SET MESSAGE_TEXT = 'Not Authorized!'; 
    END IF; 
    INSERT INTO PROFILES  
      VALUES(n.empid, n.name, n.sqlid, 
             mgrid, n.salary, n.ismgr); 
  END$

This trigger enforces the business rule that users can only insert employees as their own subordinates, assuming that they themselves are managers.

The INSTEAD OF update trigger below enforces the business rule that only subordinate employees can be updated, and if an employee is moved from one manager to another manager, both managers need to be subordinates of the user performing the update.

It is important to point out that DB2 has no way of knowing which columns actually got updated by the user. DB2 can only compare the OLD and NEW transition variables to detect changes. In this case the trigger was designed to always update the whole row. This isn't as bad for performance as it first appears, since DB2's logger can detect which portions actually changed and avoids excessive logging.

CREATE TRIGGER UPDATE_PROFILES_V 
  INSTEAD OF UPDATE ON PROFILES_V REFERENCING NEW AS n 
  OLD AS o FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    DECLARE oldmgrid, newmgrid INT; 
    SET oldmgrid = (SELECT empid FROM PROFILES  
                    WHERE name = o.mgrname), 
        newmgrid = (SELECT empid FROM PROFILES  
                    WHERE name = n.mgrname); 
    IF oldmgrid NOT IN (SELECT empid FROM my_emps) 
       OR newmgrid NOT IN (SELECT empid FROM my_emps) 
       OR o.empid = (SELECT empid FROM PROFILES P  
                     WHERE USER = P.sqlid) 
    THEN 
      SIGNAL SQLSTATE '70000'  
         SET MESSAGE_TEXT = 'Not Authorized!'; 
    END IF; 
    UPDATE PROFILES SET empid = n.empid, 
                        mgrid = newmgrid, 
                        salary = n.salary, 
                        sqlid = n.sqlid, 
                        name = n.name, 
                        ismgr = n.ismgr 
     WHERE empid = o.empid; 
  END$

Finally the INSTEAD OF delete trigger below ensures that users can only delete subordinates and never themselves.

CREATE TRIGGER DELETE_PROFILES_V 
  INSTEAD OF DELETE ON PROFILES_V 
  REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    DECLARE mgrid INT; 
    SET mgrid = (SELECT empid FROM PROFILES  
                 WHERE name = o.mgrname); 
    IF mgrid NOT IN (SELECT empid FROM my_emps) 
       OR o.empid = (SELECT empid FROM PROFILES P  
                      WHERE USER = P.sqlid) 
    THEN 
      SIGNAL SQLSTATE '70000'  
         SET MESSAGE_TEXT = 'Not Authorized!'; 
    END IF; 
    DELETE FROM PROFILES WHERE empid = o.empid; 
  END$

Having finished our DB schema and fed some initial data into the table, we can check to see that everything works:

SELECT * FROM PROFILES_V ORDER BY empid$ 
 
EMPID  NAME         MGRNAME    SALARY    SQLID    ISMGR 
-----  ------------ ---------- --------- -------- ----- 
    1  SuperBoss    -                  - sboss    Y 
 1001  BigBoss      SuperBoss          - bboss    Y 
 1002  MySelf       SuperBoss  250000.00 SRIELAU  Y 
 2001  FirstLine    BigBoss            - fline    Y 
 2002  MiddleMen    BigBoss            - mmen     Y 
 2003  Yeti         MySelf      90000.00 yeti     Y 
 2004  BigFoot      MySelf      80000.00 bfoot    N 
 3001  TinyToon     FirstLine          - ttoon    N 
 3002  Mouse        FirstLine          - Mouse    N 
 3003  Whatsisname  MiddleMen          - wname    N 
 3004  Hasnoclue    MiddleMen          - hclue    N 
 3005  Doesallwork  Yeti        15000.00 dwork    N

So far, so good. Let's see if the INSTEAD OF triggers work as designed. First we hire NewGuy and have him work for Yeti:

INSERT INTO PROFILES_V  
VALUES (3006, 'NewGuy', 'Yeti', 35000, 'nguy', 'N')$ 
 
SELECT * FROM PROFILES_V WHERE empid = 3006$ 
 
EMPID  NAME         MGRNAME    SALARY    SQLID    ISMGR 
-----  ------------ ---------- --------- -------- ----- 
 3006  NewGuy       Yeti        35000.00 nguy     N

Next we promote Doesallwork to a management role under Myself and give him a raise of 30%:

UPDATE PROFILES_V  
  SET ismgr = 'Y', 
      salary = salary * 1.30, 
      mgrname = 'MySelf' 
  WHERE name = 'Doesallwork'$ 
   
SELECT * FROM PROFILES_V WHERE name = 'Doesallwork'$ 
 
EMPID  NAME         MGRNAME    SALARY    SQLID    ISMGR 
-----  ------------ ---------- --------- -------- ----- 
 3005  Doesallwork  MySelf      19500.00 dwork    Y

Keeping this example realistic, Doesallwork comes to his senses and leaves the company:

DELETE FROM PROFILES_V WHERE name = 'Doesallwork'$

So far we had examples of views over no tables, one table, and one table involved in an outer join. Let's now investigate a multi-table join where all tables require updates.

Vertically partitioned data (or star join)

A typical scenario requiring joins and updates to all tables (at the same time) is vertical partitioning of the data. To keep things simple we use a schema of PERSONS. Some PERSONS are employed, others are enrolled as students. Some PERSONS are both employed and students.

CREATE TABLE PERSONS(ssn INT NOT NULL, 
                     name VARCHAR(20) NOT NULL)$ 
                      
CREATE TABLE EMPLOYEES(ssn INT NOT NULL,  
                      company VARCHAR(20) NOT NULL, 
                      salary DECIMAL(9,2))$ 
                       
CREATE TABLE STUDENTS(ssn INT NOT NULL, 
                      university VARCHAR(20) NOT NULL, 
                      major VARCHAR(10))$

To join all these tables together in the application can be annoying. So we create a view:

CREATE VIEW PERSONS_V(ssn, name, company,  
                      salary, university, major) 
AS SELECT P.ssn, name, company,  
          salary, university, major 
     FROM PERSONS P LEFT OUTER JOIN EMPLOYEES E  
                         ON P.ssn = E.ssn 
                    LEFT OUTER JOIN STUDENTS S 
                         ON P.ssn = S.ssn$

Again this view is neither insertable, nor updatable or deletable. So we need to generate a full set of INSTEAD OF triggers:

CREATE TRIGGER INSERT_PERSONS_V  
  INSTEAD OF INSERT ON PERSONS_V 
  REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    INSERT INTO PERSONS VALUES (n.ssn, n.name); 
    IF n.university IS NOT NULL THEN 
      INSERT INTO STUDENTS  
        VALUES(n.ssn, n.university, n.major); 
    END IF; 
    IF n.company IS NOT NULL THEN 
      INSERT INTO EMPLOYEES 
        VALUES(n.ssn, n.company, n.salary); 
    END IF; 
  END$

The trigger above considers a person a student if associated with a university and an employee if associated with a company.

It is fairly easy to implement the INSTEAD OF DELETE trigger. It is safe to delete a person from all three tables, even if there really isn't any entry:

CREATE TRIGGER DELETE_PERSONS_V  
  INSTEAD OF DELETE ON PERSONS_V 
  REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    DELETE FROM STUDENTS WHERE ssn = o.ssn; 
    DELETE FROM EMPLOYEES WHERE ssn = o.ssn; 
    DELETE FROM PERSONS WHERE ssn = o.ssn; 
  END$

Now things are getting trickier. As indicated when defining the insert trigger a student must be associated to a university and an employee to a company. So if a person gains or loses one of these associations, rows need to be added or removed from the respective tables:

CREATE TRIGGER UPDATE_PERSONS_V 
  INSTEAD OF UPDATE ON PERSONS_V 
  REFERENCING OLD AS o NEW AS n  
  FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    UPDATE PERSONS  
      SET (ssn, name) = (n.ssn, n.name)  
      WHERE ssn = o.ssn; 
    IF n.university IS NOT NULL  
       AND o.university IS NOT NULL THEN 
      UPDATE STUDENTS 
        SET (ssn, university, major) 
          = (n.ssn, n.university, n.major) 
        WHERE ssn = o.ssn; 
    ELSEIF n.university IS NULL THEN 
      DELETE FROM STUDENTS WHERE ssn = o.ssn; 
    ELSE 
      INSERT INTO STUDENTS 
        VALUES(n.ssn, n.university, n.major); 
    END IF; 
    IF n.company IS NOT NULL 
       AND o.company IS NOT NULL THEN 
      UPDATE EMPLOYEES 
        SET (ssn, company, salary) 
          = (n.ssn, n.company, n.salary) 
        WHERE ssn = o.ssn; 
    ELSEIF n.company IS NULL THEN 
      DELETE FROM EMPLOYEES WHERE ssn = o.ssn; 
    ELSE 
      INSERT INTO EMPLOYEES 
        VALUES(n.ssn, n.company, n.salary); 
    END IF; 
  END$

Now that the view is fully defined including triggers for insert, delete and update, let's try it out:

INSERT INTO PERSONS_V VALUES 
  (123456, 'Smith', NULL, NULL, NULL, NULL), 
  (234567, 'Jones', 'Wmart', 20000, NULL, NULL), 
  (345678, 'Miller', NULL, NULL, 'Harvard', 'Math'), 
  (456789, 'McNuts', 'SelfEmp', 60000, 'UCLA', 'CS')$ 
   
SELECT * FROM PERSONS_V ORDER BY SSN$ 
 
SSN     NAME    COMPANY  SALARY    UNIVERSITY  MAJOR 
------  ------  -------- --------  ----------- ------ 
123456  Smith   -               -  -           - 
234567  Jones   Wmart    20000.00  -           - 
345678  Miller  -               -  Harvard     Math 
456789  McNuts  SelfEmp  60000.00  UCLA        CS

Now Mr. Smith marries, takes on his wife's name, 'Johnson,' and gets hired by Mickburgs for a salary of 15000, while Ms. Miller finishes at Harvard and gets hired by IBM.

UPDATE PERSONS_V SET (name, company, salary)  
                   = ('Johnson', 'Mickburgs', 15000) 
  WHERE SSN = 123456$ 
   
UPDATE PERSONS_V SET (company, salary, university) 
                   = ('IBM', 70000, NULL) 
  WHERE SSN = 345678$ 
   
SELECT * FROM PERSONS_V WHERE SSN IN (123456, 345678) 
  ORDER BY SSN$ 
   
SSN     NAME     COMPANY  SALARY    UNIVERSITY  MAJOR 
------  -------  -------- --------  ----------- ------ 
123456  Johnson  Mickburgs   15000  -           - 
345678  Miller   IBM         70000  -           -

Mr. Jones leaves the country for a prologonged time:

DELETE FROM PERSONS_V WHERE NAME = 'Jones'$

On a final note, we can omit columns that are NULL on insert:

INSERT INTO PERSONS_V(ssn, name) 
  VALUES (567890, 'vanderpoor')$

Having said that, it makes sense to talk about DEFAULTS. We will do this, and more, in the next and final example.

Exception table processing

When inserting data into tables, data types, BEFORE triggers, check constraints and RI constraints are used to limit the data inserted in some way or other. SET INTEGRITY, LOAD and IMPORT all allow the user to handle bad data by storing rejected rows in an exception table. INSERT, and UPDATE, however, do not inherently have a way to handle bad data. It is the application's responsibility to attempt an action such as an insert and then, if it fails, take the necessary steps. This is inconvenient in two ways:

  • First, the processing is not encapsulated.
  • Second, single-row inserts are wasteful, even when done through a stored procedure.

The example below uses an INSTEAD OF INSERT trigger to dispatch bad rows to another location.

CREATE TABLE ADDRESSES(name varchar(10), 
                       number INT, 
                       street varchar(20), 
                       country VARCHAR(10)  
                               WITH DEFAULT 'CANADA')$ 
                                
CREATE TABLE BAD_ADDRESSES  
AS (SELECT CAST(NULL AS VARCHAR(30)) AS Reason, 
           A.* 
    FROM ADDRESSES A) DEFINITION ONLY$  
     
CREATE VIEW ADDRESSES_V 
AS SELECT * FROM ADDRESSES$ 
CREATE TRIGGER INSERT_ADDRESSES_V  
  INSTEAD OF INSERT ON ADDRESSES_V 
  REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    DECLARE reason VARCHAR(30); 
    SET reason 
      = CASE WHEN n.number IS NULL OR n.number <= 0  
              THEN 'Number' 
             WHEN n.name IS NULL OR LENGTH(n.name) = 0 
              THEN 'Name' 
             WHEN n.street IS NULL  
               OR LENGTH(n.street) = 0 
              THEN 'Street' 
             WHEN country IS NULL  
               OR country NOT IN ('CANADA', 'USA', 
                                  'GERMANY', 'FRANCE') 
              THEN 'Country' 
             ELSE NULL END; 
    IF reason IS NOT NULL THEN 
      INSERT INTO BAD_ADDRESSES  
        VALUES(reason, n.name, n.number,  
               n.street, n.country); 
    ELSE 
      INSERT INTO ADDRESSES 
        VALUES(n.name, n.number, n.street, n.country); 
    END IF; 
  END$

The trigger above will separate the good from the bad data and store the addresses accordingly. The example illustrates more, though.

It is important to note that the current DB2 V8 documentation specifies all DEFAULT values of columns shall be seen as NULL by the INSTEAD OF trigger. This behavior was changed due to DB2 V8 beta customer feedback, thus the DEFAULTS NULL clause was also pulled from the INSTEAD OF trigger specification.

When a view is defined its column defaults are inherited from the underlying tables for updatable columns. If a column is not updatable (without the help of an INSTEAD OF trigger) then the DEFAULT is considered NULL. In this example, if the country is not specified, or the keyword DEFAULT is used, the INSTEAD OF trigger will see 'CANADA.' For all other columns the trigger will see NULL.

INSERT INTO ADDRESSES_V VALUES 
 ('Jones', 510, 'Yonge St.', DEFAULT), 
 ('Smith', -1, 'Nowhere', 'USA'), 
 (NULL, 38, 'Am Feldweg', 'GERMANY'), 
 ('Poubelle', 23, 'Rue de Jardin', 'FRANCE')$ 
  
SELECT * FROM ADDRESSES ORDER BY name$ 

NAME       NUMBER  STREET         COUNTRY   
---------- ------  -------------- ------- 
Jones         510  Yonge St.      CANADA     
Poubelle       23  Rue de Jardin  FRANCE
 
SELECT * FROM BAD_ADDRESSES ORDER BY name$ 
 
REASON  NAME    NUMBER  STREET      COUNTRY   
------  ------- ------  ----------- ------- 
Number  Smith       -1  Nowhere     USA 
Name    -           38  Am Feldweg  GERMANY

The trigger could easily be adjusted to ignore the bad rows rather than storing them away.

More ideas....

Other applications of INSTEAD OF triggers might be:

  • Avoiding recursive AFTER triggers.
    Sometimes it happens that an AFTER trigger has to update its own subject table which, in extreme cases, can strain the available statement heap and compile time of the statement. Using a view over the subject table and creating an INSTEAD OF trigger allows multiple updates to the subject table without causing a recursive firing of the view.
  • Implementing external tables.
    You might want to extend the existing read access to a table UDF in order to also have insert, update and delete capabilities. It is feasible to define insert, update and delete-specific UDFs. Then you can create a view over the table UDF and define INSTEAD OF triggers to drive the other UDFs.
  • Making WebSphere® MQ look like a table.
    This would be similar to the external tables example above, but with WebSphere MQ functions.

Things to watch out for

While we tried many examples of using INSTEAD OF triggers, we have not talked about their limitations. Let's do that now.

Symmetric views
Due to the uncontrollable semantics attached to INSTEAD OF triggers, you cannot create an INSTEAD OF trigger on a symmetric view. A symmetric view is a view that accepts only rows that it can also return. Furthermore, no symmetric view can be created on top of a view that has an INSTEAD OF trigger.

Updatable cursors
INSTEAD OF triggers are currently not supported in the context of updatable cursors. This means a positioned update or positioned delete will fail if it causes an INSTEAD OF UPDATE or INSTEAD OF DELETE trigger, respectively, to fire.

Caveats

Trigger firing order
INSTEAD OF triggers are considered to fire together with AFTER triggers. This means if an update or delete on a UNION ALL view causes INSTEAD OF triggers and/or AFTER triggers to fire, the complete set of triggers will fire in order of trigger creation time.

Trigger and constraint processing
As with AFTER triggers, SQL Statements in the body of an INSTEAD OF trigger are complete semantic entities. This means, for example, an UPDATE statement inside of an INSTEAD OF trigger will cause the respective checks, RI and triggers to fire before the next statement and ultimately the next row in the transition table gets processed.

GET_DIAGNOSTICS ROW_COUNT (a.k.a., SQLCA.ERRD(3))
Prior to DB2 V8, the number of rows modified through a view was always equal to the number of rows directly updated on a base table, where "directly" means excluding rows modified by triggers and delete cascade RI constraints. With INSTEAD OF triggers this is not true anymore, and the meaning of ROW_COUNT needs to be refined. ROW_COUNT in DB2 V8 specifies the number of rows that qualify for the update, delete or insert operation as given by the user. For an INSTEAD OF trigger on such an operation, this translates to the number of times the trigger is executed and to the cardinality of the transition tables.


Potential improvements

INSTEAD OF triggers raise some interesting questions and options from an SQL language point of view. Examples are:

View-level defaults
Since INSTEAD OF triggers completely detach the semantics of a view's query from its behavior on insert, delete and update, it no longer seem sufficient to derive defaults only from the underlying tables. It seems prudent to consider views with explicit column defaults, possibly even identity column properties.

Statement-level INSTEAD OF triggers
While DB2 supports only row-level INSTEAD OF triggers today, there are various examples where a statement-level INSTEAD OF trigger might be beneficial. For example, the exception table trigger we discussed in one of the examples above would certainly perform better if processed once for a set of rows, rather than for each row.

Why only INSTEAD OF triggers on views?
Having introduced one kind of trigger to views, why stop there? Maybe BEFORE, AFTER triggers, and even IDENTITY are other candidates.


Conclusion

In this article we discussed the capabilities and limitations of updatable views. We introduced INSTEAD OF triggers, which allow you to turn any view into an updatable view, and discovered a variety of applications of this DB2 V8 for distributed platforms feature.


Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13622
ArticleTitle=INSTEAD OF Triggers - All Views are Updatable!
publish-date=10102002