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
CREATE TABLE T1 (C1 VARCHAR(10), C2 INT);
CREATE VIEW V1(X1) AS SELECT C1 FROM T1 WHERE C2 > 10;
INSERT INTO V1 VALUES('A');
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
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';
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;
DELETE FROM V3 WHERE Z1 = 'A' AND Z2 = 'X';
INSTEAD OF triggers with views defined on views
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;
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
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.