INSTEAD OF SQL triggers

 

An INSTEAD OF trigger is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.

An INSTEAD OF trigger allows a view, which is not inherently insertable, updatable, or deletable, to be inserted into, updated, or deleted from. See CREATE VIEW for more information about deleteable, updatable, and insertable views.

After an SQL INSTEAD OF trigger is added to a view, the view which previously could only be read from can be used as the target of an insert, update, or delete operation. The INSTEAD OF trigger defines the operations which need to be performed to maintain the view.

A view can be used to control access to tables. INSTEAD OF triggers can simplify the maintenance of access control to tables.

 

Using an INSTEAD OF trigger

The definition of the following view V1 is updatable, deletable, and insertable:

CREATE TABLE T1 (C1 VARCHAR(10), C2 INT)

CREATE VIEW V1(X1) AS SELECT C1 FROM T1 WHERE C2 > 10

For the following insert statement, C1 in table T1 will be assigned a value of 'A'. C2 will be assigned the NULL value. The NULL value would cause the new row to not match the selection criteria C2 > 10 for the view V1.

INSERT INTO V1 VALUES('A')

Adding the INSTEAD OF trigger IOT1 can provide a different value for the row that will be selected by the view:

CREATE TRIGGER IOT1 INSTEAD OF INSERT ON V1

REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL INSERT INTO T1 VALUES(NEW_ROW.X1, 15)

 

Making a view deletable

The definition of the following join view V3 is not updatable, deletable, or insertable:

CREATE TABLE A (A1 VARCHAR(10), A2 INT)

CREATE VIEW V1(X1) AS SELECT A1 FROM A 

CREATE TABLE B (B1 VARCHAR(10), B2 INT)

CREATE VIEW V2(Y1) AS SELECT  B1 FROM B 

CREATE VIEW V3(Z1, Z2) AS SELECT V1.X1, V2.Y1 FROM V1, V2 WHERE V1.X1 = 'A' AND V2.Y1 > 'B'

Adding the INSTEAD OF trigger IOT2 makes the view V3 deletable:

CREATE TRIGGER IOT2 INSTEAD OF DELETE ON V3

REFERENCING OLD AS OLD_ROW FOR EACH ROW MODE DB2SQL

BEGIN
  DELETE FROM A WHERE A1 = OLD_ROW.Z1;
  DELETE FROM B WHERE B1 = OLD_ROW.Z2;

END

With this trigger, the following DELETE statement is allowed. It deletes all rows from table A having an A1 value of 'A', and all rows from table B having a B1 value of 'X'.

DELETE FROM V3 WHERE Z1 = 'A' AND Z2 = 'X'

 

INSTEAD OF triggers with views defined on views

The following definition of view V2 defined on V1 is not inherently insertable, updatable, or deletable:

CREATE TABLE T1 (C1 VARCHAR(10), C2 INT)

CREATE TABLE T2 (D1 VARCHAR(10), D2 INT)

CREATE VIEW V1(X1, X2) AS SELECT C1, C2 FROM T1

UNION SELECT D1, D2 FROM T2


CREATE VIEW V2(Y1, Y2) AS SELECT X1, X2 FROM V1

Adding the INSTEAD OF trigger IOT1 to V1 does not make V2 updatable:

CREATE TRIGGER IOT1 INSTEAD OF UPDATE ON V1

REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL

BEGIN
  UPDATE T1 SET C1 = NEW_ROW.X1, C2 = NEW_ROW.X2 WHERE
    C1 = OLD_ROW.X1 AND C2 = OLD_ROW.X2;
  UPDATE T2 SET D1 = NEW_ROW.X1, D2 = NEW_ROW.D2 WHERE
    D1 = OLD_ROW.X1 AND D2 = OLD_ROW.X2;

END

View V2 remains not updatable since the original definition of view V2 remains not updatable.

 

Using INSTEAD OF triggers with BEFORE and AFTER triggers

The addition of an INSTEAD OF trigger to a view does not cause any conflicts with BEFORE and AFTER triggers defined on the base tables:

CREATE TABLE T1 (C1 VARCHAR(10), C2 DATE)

CREATE TABLE T2 (D1 VARCHAR(10))


CREATE TRIGGER AFTER1 AFTER DELETE ON T1

REFERENCING OLD AS OLD_ROW FOR EACH ROW MODE DB2SQL   DELETE FROM T2 WHERE D1 = OLD_ROW.C1


CREATE VIEW V1(X1, X2) AS SELECT SUBSTR(T1.C1, 1, 1), DAYOFWEEK_ISO(T1.C2) FROM T1


CREATE TRIGGER IOT1 INSTEAD OF DELETE ON V1

REFERENCING OLD AS OLD_ROW FOR EACH ROW MODE DB2SQL   DELETE FROM T1 WHERE C1 LIKE (OLD_ROW.X1 CONCAT '%')

Any delete operations for view V1 result in the AFTER DELETE trigger AFTER1 being activated also because trigger IOT1 performs a delete on table T1. The delete for table T1 causes the AFTER1 trigger to be activated.

 

Dependent views and INSTEAD OF triggers

When adding an INSTEAD OF trigger to a view, if the view definition references views that also have INSTEAD OF triggers defined, you should define INSTEAD OF triggers for all three operations, UPDATE, DELETE, and INSERT, to avoid confusion on what capabilities the view being defined contains versus what the capabilities of any dependent views have.

 

Parent topic:

SQL triggers