DB2 trigger on a view

This article will discuss the trigger on a view, other than a table.

Firstly, let's define updatable view, then continue define trigger on view.

1. Updatable View

   Most of, but the simplest, views are read-only, see http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_createview.html

   A view is read-only if one or more of the following statements is true of its definition:

- Start of changeThe first FROM clause identifies more than one table or view, or identifies a table function, a nested table expression, a common table expression, or a collection-derived table.End of change

- The first SELECT clause specifies the keyword DISTINCT.

- The outer fullselect contains a GROUP BY clause.

- The outer fullselect contains a HAVING clause.

- The first SELECT clause contains an aggregate function.

- It contains a subquery such that the base object of the outer fullselect, and of the subquery, is the same table.

- The first FROM clause identifies a read-only view.

- The first FROM clause identifies a system-maintained materialized query table.

- The outer fullselect is not a subselect (contains a set operator).

   Even when a view is updatable, there are difference between Deletable, Insertable, and Updatable, see https://mainframealldtime.wordpress.com/2012/05/01/views-in-db2/

2. Trigger on View

   You cannot define a general trigger on a a view, except INSTEAD OF trigger, and in fact, INSTEAD OF trigger could only be defined on view.

   INSTEAD OF triggers are triggers that execute instead of the INSERT, UPDATE, or DELETE statement that activates the trigger.

   

   INSTEAD OF trigger:

   When specify that a trigger is an INSTEAD OF trigger, DB2 will not try to interpret the view definition for the updating operation, "Instead of" doing that, DB2 will execute the trigger body actions, replaces the original INSERT, UPDATE, and DELETE action. In other words, the original INSERT, UPDATE, or DELETE operation is skipped, the trigger action will be executed.

   - Could only be defined on a view

   - Support row-level trigger only, i.e., FOR EACH ROW only

   - For each UPDATE, INSERT, and DELETE only on trigger could be created.

 

3. Cases of table trigger fire condition when updating on a view

CREATE TABLE TESTTAB(A INT, B CHAR(4), C CHAR(4) DEFAULT '0000')
CREATE VIEW  TESTVIEW(A, B) AS SELECT A, B FROM TESTTAB

-- CREATE TRIGGER ON TABLE TESTTAB
CREATE TRIGGER TESTTRG_TESTTAB AFTER INSERT ON TESTTAB REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL VALUES(SELECT 1 FROM SYSIBM.SYSDUMMY1)

INSERT INTO TESTVIEW VALUES(1, '1111')
-- TABLE TRIGGER TESTTRG_TESTTAB IS FIRED
-- THAT MEANS AS LONG AS DB2 CAN MAP THE VIEW INSERT STATEMENT INTO TABLE OPERATION, IT WORKS AS TABLE INSERT STATEMENT

-- CREATE TRIGGER ON VIEW TESTVIEW
CREATE TRIGGER TESTTRG_TESTVIEW INSTEAD OF INSERT ON TESTVIEW REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL VALUES(SELECT 1 FROM SYSIBM.SYSDUMMY1)

INSERT INTO TESTVIEW VALUES(2, '2222')
-- VIEW TRIGGER TESTTRG_TESTVIEW IS FIRED, BUT
-- TABLE TRIGGER TESTTRG_TESTTAB IS NOT FIRED

-- RECREATE TRIGGER ON VIEW TESTVIEW, WITH ACTION TO INSERT ON TABLE
DROP TRIGGER TESTTRG_TESTVIEW
CREATE TRIGGER TESTTRG_TESTVIEW INSTEAD OF INSERT ON TESTVIEW REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL INSERT INTO TESTTAB(A, B, C) VALUES(N.A, N.B, '0000')	

INSERT INTO TESTVIEW VALUES(3, '3333')
-- VIEW TRIGGER TESTTRG_TESTVIEW IS FIRED, AND
-- TABLE TRIGGER TESTTRG_TESTTAB IS FIRED TOO