Inserting, updating, and deleting data in views by using INSTEAD OF triggers

INSTEAD OF triggers are triggers that execute instead of the INSERT, UPDATE, or DELETE statement that activates the trigger. You can define these triggers on views only. Use INSTEAD OF triggers to insert, update, and delete data in complex views.

About this task

Complex views are those views that are defined on expressions or multiple tables. In some cases, those views are read only. In these cases, INSTEAD OF triggers make the insert, update and delete operations possible. If the complex view is not read only, you can request an insert, update, or delete operation. However, Db2 automatically decides how to perform that operation on the base tables that are referenced in the view. With INSTEAD OF triggers, you can define exactly how Db2 is to execute an insert, update, or delete operation on the view. You no longer leave the decision to Db2.

Procedure

To insert, update, or delete data in a view by using INSTEAD OF triggers:

  1. Define one or more INSTEAD OF triggers on the view by using a CREATE TRIGGER statement.

    You can create one trigger for each of the following operations: INSERT, UPDATE, and DELETE. These triggers define the action that Db2 is to take for each of these operations.

  2. Submit a INSERT, UPDATE, or DELETE statement on the view.

    Db2 executes the appropriate INSTEAD OF trigger.

Example

Suppose that you create the following view on the sample tables DSN8D10.EMP and DSN8D10.DEPT: 

CREATE VIEW EMPV (EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO, HIREDATE,DEPTNAME)
  AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO, HIREDATE, DEPTNAME
     FROM DSN8D10.EMP, DSN8D10.DEPT WHERE DSN8D10.EMP.WORKDEPT 
          = DSN8D10.DEPT.DEPTNO

Suppose that you also define the following three INSTEAD OF triggers:

CREATE TRIGGER EMPV_INSERT INSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP
FOR EACH ROW MODE DB2SQL
  INSERT INTO DSN8D10.EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 
                           PHONENO, HIREDATE)
    VALUES(NEWEMP.EMPNO, NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME,
       COALESCE((SELECT D.DEPTNO FROM DSN8D10.DEPT AS D
                   WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
                RAISE_ERROR('70001', 'Unknown department name')),
       NEWEMP.PHONENO, NEWEMP.HIREDATE)

CREATE TRIGGER EMPV_UPDATE INSTEAD OF UPDATE ON EMPV
REFERENCING NEW AS NEWEMP OLD AS OLDEMP
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  UPDATE DSN8D10.EMP AS E
   SET (E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT, E.PHONENO, 
        E.HIREDATE)
       = (NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME,
       COALESCE((SELECT D.DEPTNO FROM DSN8D10.DEPT AS D
                  WHERE D.DEPTNAME = OLDEMP.DEPTNAME),
                RAISE_ERROR ('70001', 'Unknown department name'))
       NEWEMP.PHONENO, NEWEMP.HIREDATE)
   WHERE NEWEMP.EMPNO = E.EMPNO;
  UPDATE DSN8D10.DEPT D SET D.DEPTNAME=NEWEMP.DEPTNAME
   WHERE D.DEPTNAME=OLDEMP.DEPTNAME;
END

CREATE TRIGGER EMPV_DELETE INSTEAD OF DELETE ON EMPV
REFERENCING OLD AS OLDEMP
FOR EACH ROW MODE DB2SQL
  DELETE FROM DSN8D10.EMP AS E WHERE E.EMPNO = OLDEMP.EMPNO

Because the view is on a query with an inner join, the view is read only. However, the INSTEAD OF triggers makes insert, update, and delete operations possible.

The following table describes what happens for various insert, update, and delete operations on the EMPV view.

Table 1. Results of INSTEAD OF triggers
SQL statement Result 
INSERT INTO EMPV VALUES (...)
 The EMPV_INSERT trigger is activated. This trigger inserts the row into the base table DSN8D10.EMP if the department name matches a value in the WORKDEPT column in the DSN8D10.DEPT table. Otherwise, an error is returned. If a query had been used instead of a VALUES clause on the INSERT statement, the trigger body would be processed for each row from the query. 
UPDATE EMPV 
   SET DEPTNAME='PLANNING & STRATEGY'
   WHERE DEPTNAME='PLANNING'
 The EMPV_UPDATE trigger is activated. This trigger updates the DEPTNAME column in the DSN8D10.DEPT for the any qualifying rows. 
DELETE FROM EMPV
  WHERE HIREDATE<'1910-01-01'
 The EMPV_DELETE trigger is activated. This trigger deletes the qualifying rows from the DSN8D10.EMP table.