DB2 10.5 for Linux, UNIX, and Windows

INSTEAD OF triggers

INSTEAD OF triggers describe how to perform insert, update, and delete operations against complex views. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).

Usually, INSTEAD OF triggers contain the inverse of the logic applied in a view body. For example, consider a view that decrypts columns from its source table. The INSTEAD OF trigger for this view encrypts data and then inserts it into the source table, thus performing the symmetrical operation.

Using an INSTEAD OF trigger, the requested modify operation against the view gets replaced by the trigger logic, which performs the operation on behalf of the view. From the perspective of the application this happens transparently, as it perceives that all operations are performed against the view. Only one INSTEAD OF trigger is allowed for each kind of operation on a given subject view.

The view itself must be an untyped view or an alias that resolves to an untyped view. Also, it cannot be a view that is defined using WITH CHECK OPTION (a symmetric view) or a view on which a symmetric view has been defined directly or indirectly.

Example

The following example presents three INSTEAD OF triggers that provide logic for INSERTs, UPDATEs, and DELETEs to the defined view (EMPV). The view EMPV contains a join in its from clause and therefore cannot natively support any modify operation.

   CREATE VIEW EMPV(EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO,
                    HIREDATE, DEPTNAME)
   AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO,
             HIREDATE, DEPTNAME 
             FROM EMPLOYEE, DEPARTMENT WHERE
                  EMPLOYEE.WORKDEPT = DEPARTMENT.DEPTNO
   
   CREATE TRIGGER EMPV_INSERT INSTEAD OF INSERT ON EMPV
   REFERENCING NEW AS NEWEMP FOR EACH ROW
   INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
                         WORKDEPT, PHONENO, HIREDATE)
           VALUES(EMPNO, FIRSTNME, MIDINIT, LASTNAME, 
                  COALESCE((SELECT DEPTNO FROM DEPARTMENT AS D
                            WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
                           RAISE_ERROR('70001', 'Unknown dept name')),
                  PHONENO, HIREDATE)
   
   CREATE TRIGGER EMPV_UPDATE INSTEAD OF UPDATE ON EMPV
   REFERENCING NEW AS NEWEMP OLD AS OLDEMP
     FOR EACH ROW
   BEGIN ATOMIC
    VALUES(CASE WHEN NEWEMP.EMPNO = OLDEMP.EMPNO THEN 0 
                ELSE RAISE_ERROR('70002', 'Must not change EMPNO') END);
    UPDATE EMPLOYEE AS E
      SET (FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE)
        = (NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME, 
                  COALESCE((SELECT DEPTNO FROM DEPARTMENT AS D
                            WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
                           RAISE_ERROR ('70001', 'Unknown dept name')),
                  NEWEMP.PHONENO, NEWEMP.HIREDATE)
    WHERE NEWEMP.EMPNO = E.EMPNO;
   END
   
   CREATE TRIGGER EMPV_DELETE INSTEAD OF DELETE ON EMPV
   REFERENCING OLD AS OLDEMP FOR EACH ROW
   DELETE FROM EMPLOYEE AS E WHERE E.EMPNO = OLDEMP.EMPNO