Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable. This article assesses which views are updatable on their own and then introduces INSTEAD OF triggers, a feature new with DB2® Universal DatabaseTM V8.1 for Linux, UNIX®, and Windows® that makes all views updatable.
Many examples in this article use the SQL Procedural Language (SQL PL). Since SQL PL uses the semicolon (;) in its body to separate individual statements, you must use an alternate delimiter for CLP or the Command Center. Throughout this paper we will use the dollar sign ($) to delimit statements.
To start a CLP session for working with the examples in this paper, type: db2 -td$ from your shell. For the Command Center, set and activate the delimiter by clicking on Tools -> ToolsSettings -> Use statement termination character.
A tour through updatable views
Before we can take you on a tour through updatable views, we need to get some basic understanding of what "updatable" means and what the basic problems are that DB2 (or any database management system, for that matter) has to solve to update rows in the database through a view. To begin with, by default all the information DB2 has about a view is its definition, namely the query that specifies the table derived by the view.
To be precise we need to distinguish between three levels of updatability:
Deletable: In order for DB2 to delete a row from a view it must be able to map the row specified in the view to one, and only one, row in a base table.
Updatable : In order to update a column in a view, DB2 must not only be able to map the row specified in the view to one row in a base table, it also must be able to map the column to be updated to a column in a base table. Hence all views that are updatable must by definition also be deletable.
Insertable: In order for a row to be inserted into a view, DB2 must be able to map the new row to a table and to map all the columns specified to columns in that table. Thus all views that are insertable are by definition updatable and hence deletable.
Let's now define a few tables and then investigate the updatability of various views of those tables:
CREATE TABLE T1(c1 INT, c2 FLOAT)$
INSERT INTO T1 VALUES (5, 6.0),
(6, 7.0),
(5, 6.0)$
CREATE TABLE T2(c1 INT, c2 FLOAT)$
INSERT INTO T2 VALUES (5, 9.0),
(5, 4.0),
(7, 5.0)$ |
For all examples in this article, let's assume that the data as below is present. The examples shall not influence each other. You can do this by turning auto commit OFF using the +c option in the CLP, or from the Command Center deactivate Options -> Execution -> Automatically Commit. Use the ROLLBACK command after each example.
Example 1:
CREATE VIEW V1(c1) AS SELECT c1 FROM T1 WHERE c2 > 0$ |
This is a very simple view. The derived table contains a subset of the rows and a subset of the columns of T1. This view is deletable since DB2 can keep track of the origin of each of the rows and delete the respective row in the base table.
DELETE FROM V1 WHERE c1 = 6$ |
Note, however, that if one performed a positioned delete and deleted one row where V1.c1 equals 5, there is no telling which of the two rows where T1.c1 equals 5 will be deleted.
The view is also updatable:
UPDATE V1 SET c1 = c1 + 5 WHERE c1 = 5$ |
V1.c1 can be directly mapped to T1.c1.
What about INSERT? Clearly any row inserted into V1 should be inserted into T1. The value specified for V1.c1 should be used for T1.c1. But what about T1.c2? Which values will be used? After all, the view does not know of this column and thus the user could not specify a value for it.
INSERT INTO V1 VALUES (8)$ |
Here a definition is required, and the SQL Standard has provided it: Each column value that is not provided is initialized to the implicit or explicit default of the column. Since no explicit default was specified for T1.c2, the value NULL is chosen and (8, NULL) is inserted into T1.
What happens when there is an expression?
CREATE VIEW V2(c1, c2) AS SELECT c1, c2 * c2 FROM T1$ |
Compared to the first example, nothing has changed with respect to deletablity. DB2 still knows which row in the view was produced by which row in the base table. The V2.c1 column is updatable and therefore the view is updatable.
However, V2.c2 is not updatable. The reason is that there is no way to decide the value of T1.c2 from any given V2.c2. DB2 could try to use the inverse operation of the multiplication to find matching values; however, even this simple example makes clear that such mathematical approaches can only work in the most trivial cases. The mere attempt to explain the capabilities and limits of such an algorithm shows its lack of usability.
What does all this mean to insertability? Prior to DB2 V8, a view was required to have all columns updatable in order to be insertable. In DB2 V8 it is sufficient to have just one updatable column. As long as the other columns are not specified they will be ignored.
INSERT INTO V2(c1) VALUES (7)$ |
The system will insert (7, NULL) into T1. Note that the view would be deletable even if no column had been updatable. By the same reason the only argument against inserting into a view with no updatable columns is that neither VALUES nor SELECT is defined without a single column.
Example 3:
CREATE VIEW V3(c1, c2, c3)
AS SELECT T1.c1, T1.c2, T2.c2
FROM T1, T2 WHERE T1.c1 = T2.c1$ |
This view is derived from a join. In this case its result is:
SELECT * FROM V3 ORDER BY c1, c2, c3$ C1 C2 C3 -- --- --- 5 6.0 4.0 5 6.0 4.0 5 6.0 9.0 5 6.0 9.0 |
This view is not deletable. While each row in the view can be traced back to one row in each of the tables T1 and T2, deleting the first row (5, 6.0, 4.0) by deleting the respective rows in T1 and T2 would also, indirectly, delete the second (5, 6.0, 4.0) and one of the (5, 6.0, 9.0) rows. This behavior is not intuitively clear, in particular for a user who doesn't know the view's query.
There are cases in which a deleted row in the view results in one row deleted in the base table without having an undesired impact on the view. This would be the case if, for example, both T1.c1 and T2.c1 were unique. DB2 today does not consider this special case.
Since V3 is not deletable it is also not updatable. Again, strange things would happen if you attempted to update one row in the view, or even worse, update V1.c1 to different values for different rows in the view.
Similarly the semantics of INSERT is not clear. Adding another row (5, 6.0, 9.0) could have varying outcomes, depending on the semantics chosen for the base table inserts. Does this mean that no updatable view can refer to another table? Not so. As discussed in Example 2, views with expressions are very well updatable. So a scalar subquery is perfectly in order.
Example 4:
CREATE VIEW V4(c1, c2) AS SELECT c1, c2 FROM T1 UNION ALL SELECT c1, c2 FROM T2$ |
Every row in V4 clearly originated from one row in a specific table. For that reason a view based on UNION ALL is deletable. If a column is not based on an expression, then the column is also updatable. However, the view is not insertable for the obvious reason: it cannot be determined into which base table any given row should be inserted. Allowing a row into both base tables is not satisfactory because a subsequent select from the view would show the row twice. To allow INSERT through UNION ALL, constraints are required on the base tables that dispatch any given row to exactly one table.
See the article "Updatable UNION ALL Views in DB2 Universal Database Version 8" (developerWorks, September 2002) for more details on INSERT through UNION ALL views.
There are some restrictions to views on UNION ALL that should be noted. A column of a UNION ALL view is not updatable even if DB2 itself injected an expression.
CREATE VIEW V5(c1, c2) AS SELECT c2, c1 FROM T1 UNION ALL SELECT c1, c2 FROM T2$ |
DB2 had to cast both T2.c1 and T1.c1 to FLOAT. Both V5.c1 and V5.c2 are now based on expressions and thus not updatable. Nonetheless, V5 is still deletable.
CREATE VIEW V6(c1, c2) AS SELECT c1, c2 FROM T1 UNION ALL SELECT c1, c2 FROM T1$ |
A view like V6 is also called a "diamond" because the processing fans out from a single source into two operations (SELECT) and then comes back together again (UNION ALL). Diamonds are read-only. The rows cannot even be deleted. The reason is that each row from T1 is represented twice in V6. So it is not possible to delete just one row in V6. Also it is not possible to update one row only.
The reverse problem arises when a UNION (or DISTINCT) is used. Now each row in the view can be mapped to potentially many rows in the base table. Should only one row or all matching rows in the base table be deleted?
We have discussed the idea behind the updatability of a few different kinds of views. There are other views that are not updatable; however, let's move on to the big equalizer of view updatability: INSTEAD OF triggers.
A view is defined by its body. This is very obvious for SELECT queries. For UPDATE, DELETE and INSERT we discussed above that, while for some classes it is possible to make assumptions on how the view defines obvious semantics, for most cases it does not. This is where INSTEAD OF triggers jump into the breach. An INSTEAD OF trigger prevents DB2 from trying to interpret the view definition for the update operation. "Instead of" doing that, it will execute the body of the trigger, relying on the definer to come up with meaningful semantics.
For example:
CREATE VIEW V7(c1, c2) AS SELECT DISTINCT c1, c2 FROM T1$ |
An INSTEAD OF trigger can be defined to delete all rows in T1 matching a given row in V10 or to delete only one according to some predetermined rule.
Let's define a trigger that deletes all matching rows in the base table:
CREATE TRIGGER V7_DELETE INSTEAD OF DELETE ON V7 REFERENCING OLD_TABLE AS OLD_TAB OLD AS O FOR EACH ROW MODE DB2SQL DELETE FROM T1 WHERE o.c1 = c1 AND o.c2 = c2$ |
Superficially, only two clauses have changed compared to a normal trigger. First we defined the keywords INSTEAD OF. Second we specified the name of a view rather than a base table.
INSTEAD OF is a very clear clause. It does not mean execute the trigger before attempting the delete. It doesn't mean do it after. It literally means, forget about the delete. Execute this piece of code instead.
INSTEAD OF triggers are always created for views, never for base tables or nicknames. INSTEAD OF triggers in DB2 V8 are always FOR EACH ROW triggers. This means the trigger is executed once for each row that qualifies for the delete, update or insert operation against the view.
The concept of transition tables and transition variables holds just the same as for normal triggers. Table 1, below, shows where the transition variables and tables can be used, depending on the type of INSTEAD OF trigger:
| OLD | OLD_TABLE | NEW | NEW_TABLE | |
|---|---|---|---|---|
| INSERT | NO | NO | YES | YES |
| UPDATE | YES | YES | YES | YES |
| DELETE | YES | YES | NO | NO |
OLD_TABLE and NEW_TABLE transition tables specify the set of rows being processed by the view. OLD and NEW transition variables are the specific rows in the respective transition tables processed by the current invocation of the trigger.
In a DELETE or UPDATE trigger the OLD_TABLE is the set of rows that qualified for the delete. This means they are the rows as a cursor over the view sees them. Here are the values for OLD_TABLE in our example:
OLD_TAB: C1 C2 --- ---- 5 6.0 6 7.0 |
The order in which rows are processed by the trigger is arbitrary. This means there is no rule whether the above trigger will see (5, 6.0) or (6, 7.0) first as the OLD transition row. It is further important to note that for all triggers, including INSTEAD OF triggers, the transition tables are completely determined before the trigger is ever fired. This means we can delete from T1 without seeing changes in OLD_TABLE.
Note that the trigger above cannot have a WHEN clause in the triggered action. INSTEAD OF triggers fire unconditionally. The same is true for column lists typical for BEFORE and AFTER UPDATE triggers. These, too, are a kind of trigger condition and are not allowed for INSTEAD OF triggers.
CREATE TRIGGER V7_UPDATE INSTEAD OF UPDATE ON V7 REFERENCING NEW AS n OLD AS o FOR EACH ROW MODE DB2SQL UPDATE T1 SET (c1, c2) = (n.c1, n.c2) WHERE c1 = o.c1 AND c2 = o.c2$ |
The trigger will fire no matter which column is updated.
Diagram 1 shows the complete syntax diagram for INSTEAD OF triggers:
Diagram 1. Syntax diagram for INSTEAD OF triggers
>>-CREATE TRIGGER--trigger-name--INSTEAD OF--+-INSERT-+--ON--view-name------->
+-DELETE-+
'-UPDATE-'
>-----+---------------------------------------------------------------------+>
| .----------------------------------. |
| V .-AS-. | |
'-REFERENCING---+-OLD--+----+--correlation-name-+-+-+---------------+-'
| .-AS-. |
+-NEW-+----+--correlation-name--+
| .-AS-. |
+-OLD_TABLE-+----+--identifier--+
| .-AS-. |
'-NEW_TABLE-+----+--identifier--'
>----FOR EACH ROW---MODE DB2SQL----SQL-procedure-statement------------------><
|
Note: The DB2 V8.1 documentation shows the need for a DEFAULTS NULL clause. This clause has been removed for V8.1. See the Exception table example below for the behavior of defaults in the context of INSTEAD OF triggers.
The SQL-procedure-statement can be either an SQL statement such as INSERT, UPDATE, DELETE, SELECT or VALUES, or a dynamic compound statement using BEGIN ATOMIC ... END.
But enough of the theory. Let's go through some examples that you just might come across in the real world.
INSTEAD OF triggers can be as good as you make them and as bad as you allow them to be. The only connection between the definition of a view and the INSTEAD OF trigger is the row definition (signature) of the transition tables that match the column types and names as inherited from the views query.
What does this mean? If you define an INSTEAD OF trigger you can do whatever you please.
CREATE VIEW GARBAGE(text)
AS SELECT CAST(NULL AS CLOB(20M))
FROM SYSIBM.SYSDUMMY1
WHERE 1 = 0$
CREATE TRIGGER INSERT_GARBAGE
INSTEAD OF INSERT ON GARBAGE FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
END$
INSERT INTO GARBAGE VALUES 'unrecoverable document'$ |
Well, we got ourselves a nice trash bin here. For this reason only users with CONTROL privileges on the view, the view definer, the SYSADM, or the DBADM are allowed to create an INSTEAD OF trigger on the view.
After this most trivial of triggers, lets move on to a more common variety, namely using INSTEAD OF triggers to supply the "inverse" operation for an encryption expression in a view.
CREATE TABLE USERS
(user VARCHAR(20),
system VARCHAR(30),
login VARCHAR(20),
password VARCHAR(40) FOR BIT DATA)$ |
The table above holds IDs and encrypted passwords for users on different systems. The following view decrypts the rows associated with the current user given the user supplies the correct credentials.
CREATE VIEW MY_LOGINS(system, login, password)
AS SELECT system, login, decrypt_char(password)
FROM USERS AS u WHERE u.user = USER$
|
In order to update or insert through this view we need to define INSTEAD OF triggers which encrypt the user-provided passwords for storage in the base table.
CREATE TRIGGER INSERT_MY_LOGINS INSTEAD OF INSERT
ON MY_LOGINS REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
INSERT INTO USERS
VALUES(USER, n.system, n.login,
encrypt(password))$
CREATE TRIGGER UPDATE_MY_LOGINS INSTEAD OF UPDATE
ON MY_LOGINS REFERENCING OLD AS o NEW AS n
FOR EACH ROW MODE DB2SQL
UPDATE USERS U
SET system = n.system,
login = n.login,
password = encrypt(n.password)
WHERE system = o.system
AND login = o.login
AND U.user = USER$ |
Encrypted data is protected through the ENCRYPTION PASSWORD special register.
SET ENCRYPTION PASSWORD = 'unbreakable'$ |
Now we fill in the table with our confidential information:
INSERT INTO MY_LOGINS
VALUES('AFS', 'srielau', 'mydogsname'),
('Linux', 'root', 'oopsIforgot'),
('IIUG', 'Rielau', '123456789')$
SELECT * FROM MY_LOGINS WHERE system = 'Linux'$
SYSTEM LOGIN PASSWORD
------ ------- ------------
Linux root oopsIforgot |
On the base table the password is encrypted. No one, not even DB2 support, can get it back without the password set above.
SELECT * FROM USERS U ORDER BY U.user, system, login$ USER SYSTEM LOGIN PASSWORD -------- ------- ------- ------------- SRIELAU AFS srielau 0x........... SRIELAU IIUG Rielau 0x........... SRIELAU Linux root 0x........... |
It is possible to update the password or any other field in the view.
UPDATE MY_LOGINS SET password = 'mycatsname' WHERE system = 'AFS' AND login = 'srielau'$ |
The view, however, was deletable in the first place. So we can delete entries without an INSTEAD OF DELETE trigger.
DELETE FROM my_logins WHERE SYSTEM = 'AFS'$ |
For more details on encryption refer to the article "Encrypting Data Values in DB2 Universal Database" (developerWorks, August 2001).
Let's crank up the complexity a notch with a more sophisticated security example.
A manager can view the salaries of his employees, but not those of the employees of his peer managers, or his peer manager's salary. The second-line manager can see the salary of her direct as well as indirect reports.
Let's model such a schema and then define INSTEAD OF triggers that allow managers to update employee information of their charges.
CREATE TABLE PROFILES (empid INT, name VARCHAR(20), sqlid VARCHAR(18), mgrid INT, salary DECIMAL(9,2), ismgr CHAR(1))$ |
We want to seed some data, in this case a four-level hierarchy. We are a second-line manager named 'MySelf.'
INSERT INTO PROFILES
VALUES(0001, 'SuperBoss', 'sboss', NULL, 500000, 'Y'),
(1001, 'BigBoss', 'bboss', 0001, 200000, 'Y'),
(1002, 'MySelf', USER, 0001, 250000, 'Y'),
(2001, 'FirstLine', 'fline', 1001, 100000, 'Y'),
(2002, 'MiddleMen', 'mmen', 1001, 110000, 'Y'),
(2003, 'Yeti', 'yeti', 1002, 90000, 'Y'),
(2004, 'BigFoot', 'bfoot', 1002, 80000, 'N'),
(3001, 'TinyToon', 'ttoon', 2001, 50000, 'N'),
(3002, 'Mouse', 'Mouse', 2001, 40000, 'N'),
(3003, 'Whatsisname','wname', 2002, 45000, 'N'),
(3004, 'Hasnoclue', 'hclue', 2002, 38000, 'N'),
(3005, 'Doesallwork','dwork', 2003, 15000, 'N')$ |
A recursive view allows us to determine all our employees' salaries, including our own.
CREATE VIEW my_emps(empid, level, salary)
AS WITH rec(empid, level, salary)
AS (SELECT empid, 0, salary FROM PROFILES
WHERE sqlid = USER
UNION ALL
SELECT P.empid, level-1, P.salary
FROM PROFILES P, REC R
WHERE level > -100
AND R.EMPID = P.MGRID)
SELECT empid, level, salary FROM rec$ |
The view below will be the interface used by anyone reading employee information. We consider the employee id(empid), the employee name(name), their manager's name(name), their SQLID(sqlid) and whether they are managers or not(ismgr), public information. Employees can only see their own salaries and those of their subordinates.
CREATE VIEW PROFILES_V(empid, name, mgrname,
salary, sqlid, ismgr)
AS SELECT P.empid, P.name,
(SELECT name FROM PROFILES M
WHERE M.empid = P.mgrid),
ME.salary, P.sqlid, P.ismgr
FROM PROFILES P LEFT OUTER JOIN MY_EMPS ME
ON ME.empid = P.empid$ |
From our analysis earlier we know that this view is not deletable and therefore not updatable or insertable. In order to keep encapsulation, we need to define a full set of INSTEAD OF triggers:
CREATE TRIGGER INSERT_PROFILES_V
INSTEAD OF INSERT ON PROFILES_V REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE mgrid INT;
DECLARE ismgr CHAR(1);
SET (mgrid, ismgr)
= (SELECT empid, ismgr FROM PROFILES
WHERE name = n.mgrname);
IF mgrid NOT IN (SELECT empid FROM my_emps)
OR ismgr = 'N'
THEN
SIGNAL SQLSTATE '70000'
SET MESSAGE_TEXT = 'Not Authorized!';
END IF;
INSERT INTO PROFILES
VALUES(n.empid, n.name, n.sqlid,
mgrid, n.salary, n.ismgr);
END$ |
This trigger enforces the business rule that users can only insert employees as their own subordinates, assuming that they themselves are managers.
The INSTEAD OF update trigger below enforces the business rule that only subordinate employees can be updated, and if an employee is moved from one manager to another manager, both managers need to be subordinates of the user performing the update.
It is important to point out that DB2 has no way of knowing which columns actually got updated by the user. DB2 can only compare the OLD and NEW transition variables to detect changes. In this case the trigger was designed to always update the whole row. This isn't as bad for performance as it first appears, since DB2's logger can detect which portions actually changed and avoids excessive logging.
CREATE TRIGGER UPDATE_PROFILES_V
INSTEAD OF UPDATE ON PROFILES_V REFERENCING NEW AS n
OLD AS o FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE oldmgrid, newmgrid INT;
SET oldmgrid = (SELECT empid FROM PROFILES
WHERE name = o.mgrname),
newmgrid = (SELECT empid FROM PROFILES
WHERE name = n.mgrname);
IF oldmgrid NOT IN (SELECT empid FROM my_emps)
OR newmgrid NOT IN (SELECT empid FROM my_emps)
OR o.empid = (SELECT empid FROM PROFILES P
WHERE USER = P.sqlid)
THEN
SIGNAL SQLSTATE '70000'
SET MESSAGE_TEXT = 'Not Authorized!';
END IF;
UPDATE PROFILES SET empid = n.empid,
mgrid = newmgrid,
salary = n.salary,
sqlid = n.sqlid,
name = n.name,
ismgr = n.ismgr
WHERE empid = o.empid;
END$ |
Finally the INSTEAD OF delete trigger below ensures that users can only delete subordinates and never themselves.
CREATE TRIGGER DELETE_PROFILES_V
INSTEAD OF DELETE ON PROFILES_V
REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE mgrid INT;
SET mgrid = (SELECT empid FROM PROFILES
WHERE name = o.mgrname);
IF mgrid NOT IN (SELECT empid FROM my_emps)
OR o.empid = (SELECT empid FROM PROFILES P
WHERE USER = P.sqlid)
THEN
SIGNAL SQLSTATE '70000'
SET MESSAGE_TEXT = 'Not Authorized!';
END IF;
DELETE FROM PROFILES WHERE empid = o.empid;
END$ |
Having finished our DB schema and fed some initial data into the table, we can check to see that everything works:
SELECT * FROM PROFILES_V ORDER BY empid$
EMPID NAME MGRNAME SALARY SQLID ISMGR
----- ------------ ---------- --------- -------- -----
1 SuperBoss - - sboss Y
1001 BigBoss SuperBoss - bboss Y
1002 MySelf SuperBoss 250000.00 SRIELAU Y
2001 FirstLine BigBoss - fline Y
2002 MiddleMen BigBoss - mmen Y
2003 Yeti MySelf 90000.00 yeti Y
2004 BigFoot MySelf 80000.00 bfoot N
3001 TinyToon FirstLine - ttoon N
3002 Mouse FirstLine - Mouse N
3003 Whatsisname MiddleMen - wname N
3004 Hasnoclue MiddleMen - hclue N
3005 Doesallwork Yeti 15000.00 dwork N |
So far, so good. Let's see if the INSTEAD OF triggers work as designed. First we hire NewGuy and have him work for Yeti:
INSERT INTO PROFILES_V VALUES (3006, 'NewGuy', 'Yeti', 35000, 'nguy', 'N')$ SELECT * FROM PROFILES_V WHERE empid = 3006$ EMPID NAME MGRNAME SALARY SQLID ISMGR ----- ------------ ---------- --------- -------- ----- 3006 NewGuy Yeti 35000.00 nguy N |
Next we promote Doesallwork to a management role under Myself and give him a raise of 30%:
UPDATE PROFILES_V
SET ismgr = 'Y',
salary = salary * 1.30,
mgrname = 'MySelf'
WHERE name = 'Doesallwork'$
SELECT * FROM PROFILES_V WHERE name = 'Doesallwork'$
EMPID NAME MGRNAME SALARY SQLID ISMGR
----- ------------ ---------- --------- -------- -----
3005 Doesallwork MySelf 19500.00 dwork Y |
Keeping this example realistic, Doesallwork comes to his senses and leaves the company:
DELETE FROM PROFILES_V WHERE name = 'Doesallwork'$ |
So far we had examples of views over no tables, one table, and one table involved in an outer join. Let's now investigate a multi-table join where all tables require updates.
Vertically partitioned data (or star join)
A typical scenario requiring joins and updates to all tables (at the same time) is vertical partitioning of the data. To keep things simple we use a schema of PERSONS. Some PERSONS are employed, others are enrolled as students. Some PERSONS are both employed and students.
CREATE TABLE PERSONS(ssn INT NOT NULL,
name VARCHAR(20) NOT NULL)$
CREATE TABLE EMPLOYEES(ssn INT NOT NULL,
company VARCHAR(20) NOT NULL,
salary DECIMAL(9,2))$
CREATE TABLE STUDENTS(ssn INT NOT NULL,
university VARCHAR(20) NOT NULL,
major VARCHAR(10))$ |
To join all these tables together in the application can be annoying. So we create a view:
CREATE VIEW PERSONS_V(ssn, name, company,
salary, university, major)
AS SELECT P.ssn, name, company,
salary, university, major
FROM PERSONS P LEFT OUTER JOIN EMPLOYEES E
ON P.ssn = E.ssn
LEFT OUTER JOIN STUDENTS S
ON P.ssn = S.ssn$ |
Again this view is neither insertable, nor updatable or deletable. So we need to generate a full set of INSTEAD OF triggers:
CREATE TRIGGER INSERT_PERSONS_V
INSTEAD OF INSERT ON PERSONS_V
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO PERSONS VALUES (n.ssn, n.name);
IF n.university IS NOT NULL THEN
INSERT INTO STUDENTS
VALUES(n.ssn, n.university, n.major);
END IF;
IF n.company IS NOT NULL THEN
INSERT INTO EMPLOYEES
VALUES(n.ssn, n.company, n.salary);
END IF;
END$ |
The trigger above considers a person a student if associated with a university and an employee if associated with a company.
It is fairly easy to implement the INSTEAD OF DELETE trigger. It is safe to delete a person from all three tables, even if there really isn't any entry:
CREATE TRIGGER DELETE_PERSONS_V
INSTEAD OF DELETE ON PERSONS_V
REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DELETE FROM STUDENTS WHERE ssn = o.ssn;
DELETE FROM EMPLOYEES WHERE ssn = o.ssn;
DELETE FROM PERSONS WHERE ssn = o.ssn;
END$ |
Now things are getting trickier. As indicated when defining the insert trigger a student must be associated to a university and an employee to a company. So if a person gains or loses one of these associations, rows need to be added or removed from the respective tables:
CREATE TRIGGER UPDATE_PERSONS_V
INSTEAD OF UPDATE ON PERSONS_V
REFERENCING OLD AS o NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE PERSONS
SET (ssn, name) = (n.ssn, n.name)
WHERE ssn = o.ssn;
IF n.university IS NOT NULL
AND o.university IS NOT NULL THEN
UPDATE STUDENTS
SET (ssn, university, major)
= (n.ssn, n.university, n.major)
WHERE ssn = o.ssn;
ELSEIF n.university IS NULL THEN
DELETE FROM STUDENTS WHERE ssn = o.ssn;
ELSE
INSERT INTO STUDENTS
VALUES(n.ssn, n.university, n.major);
END IF;
IF n.company IS NOT NULL
AND o.company IS NOT NULL THEN
UPDATE EMPLOYEES
SET (ssn, company, salary)
= (n.ssn, n.company, n.salary)
WHERE ssn = o.ssn;
ELSEIF n.company IS NULL THEN
DELETE FROM EMPLOYEES WHERE ssn = o.ssn;
ELSE
INSERT INTO EMPLOYEES
VALUES(n.ssn, n.company, n.salary);
END IF;
END$ |
Now that the view is fully defined including triggers for insert, delete and update, let's try it out:
INSERT INTO PERSONS_V VALUES (123456, 'Smith', NULL, NULL, NULL, NULL), (234567, 'Jones', 'Wmart', 20000, NULL, NULL), (345678, 'Miller', NULL, NULL, 'Harvard', 'Math'), (456789, 'McNuts', 'SelfEmp', 60000, 'UCLA', 'CS')$ SELECT * FROM PERSONS_V ORDER BY SSN$ SSN NAME COMPANY SALARY UNIVERSITY MAJOR ------ ------ -------- -------- ----------- ------ 123456 Smith - - - - 234567 Jones Wmart 20000.00 - - 345678 Miller - - Harvard Math 456789 McNuts SelfEmp 60000.00 UCLA CS |
Now Mr. Smith marries, takes on his wife's name, 'Johnson,' and gets hired by Mickburgs for a salary of 15000, while Ms. Miller finishes at Harvard and gets hired by IBM.
UPDATE PERSONS_V SET (name, company, salary)
= ('Johnson', 'Mickburgs', 15000)
WHERE SSN = 123456$
UPDATE PERSONS_V SET (company, salary, university)
= ('IBM', 70000, NULL)
WHERE SSN = 345678$
SELECT * FROM PERSONS_V WHERE SSN IN (123456, 345678)
ORDER BY SSN$
SSN NAME COMPANY SALARY UNIVERSITY MAJOR
------ ------- -------- -------- ----------- ------
123456 Johnson Mickburgs 15000 - -
345678 Miller IBM 70000 - - |
Mr. Jones leaves the country for a prologonged time:
DELETE FROM PERSONS_V WHERE NAME = 'Jones'$ |
On a final note, we can omit columns that are NULL on insert:
INSERT INTO PERSONS_V(ssn, name) VALUES (567890, 'vanderpoor')$ |
Having said that, it makes sense to talk about DEFAULTS. We will do this, and more, in the next and final example.
When inserting data into tables, data types, BEFORE triggers, check constraints and RI constraints are used to limit the data inserted in some way or other. SET INTEGRITY, LOAD and IMPORT all allow the user to handle bad data by storing rejected rows in an exception table. INSERT, and UPDATE, however, do not inherently have a way to handle bad data. It is the application's responsibility to attempt an action such as an insert and then, if it fails, take the necessary steps. This is inconvenient in two ways:
- First, the processing is not encapsulated.
- Second, single-row inserts are wasteful, even when done through a stored procedure.
The example below uses an INSTEAD OF INSERT trigger to dispatch bad rows to another location.
CREATE TABLE ADDRESSES(name varchar(10),
number INT,
street varchar(20),
country VARCHAR(10)
WITH DEFAULT 'CANADA')$
CREATE TABLE BAD_ADDRESSES
AS (SELECT CAST(NULL AS VARCHAR(30)) AS Reason,
A.*
FROM ADDRESSES A) DEFINITION ONLY$
CREATE VIEW ADDRESSES_V
AS SELECT * FROM ADDRESSES$
CREATE TRIGGER INSERT_ADDRESSES_V
INSTEAD OF INSERT ON ADDRESSES_V
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(30);
SET reason
= CASE WHEN n.number IS NULL OR n.number <= 0
THEN 'Number'
WHEN n.name IS NULL OR LENGTH(n.name) = 0
THEN 'Name'
WHEN n.street IS NULL
OR LENGTH(n.street) = 0
THEN 'Street'
WHEN country IS NULL
OR country NOT IN ('CANADA', 'USA',
'GERMANY', 'FRANCE')
THEN 'Country'
ELSE NULL END;
IF reason IS NOT NULL THEN
INSERT INTO BAD_ADDRESSES
VALUES(reason, n.name, n.number,
n.street, n.country);
ELSE
INSERT INTO ADDRESSES
VALUES(n.name, n.number, n.street, n.country);
END IF;
END$ |
The trigger above will separate the good from the bad data and store the addresses accordingly. The example illustrates more, though.
It is important to note that the current DB2 V8 documentation specifies all DEFAULT values of columns shall be seen as NULL by the INSTEAD OF trigger. This behavior was changed due to DB2 V8 beta customer feedback, thus the DEFAULTS NULL clause was also pulled from the INSTEAD OF trigger specification.
When a view is defined its column defaults are inherited from the underlying tables for updatable columns. If a column is not updatable (without the help of an INSTEAD OF trigger) then the DEFAULT is considered NULL. In this example, if the country is not specified, or the keyword DEFAULT is used, the INSTEAD OF trigger will see 'CANADA.' For all other columns the trigger will see NULL.
INSERT INTO ADDRESSES_V VALUES
('Jones', 510, 'Yonge St.', DEFAULT),
('Smith', -1, 'Nowhere', 'USA'),
(NULL, 38, 'Am Feldweg', 'GERMANY'),
('Poubelle', 23, 'Rue de Jardin', 'FRANCE')$
SELECT * FROM ADDRESSES ORDER BY name$
NAME NUMBER STREET COUNTRY
---------- ------ -------------- -------
Jones 510 Yonge St. CANADA
Poubelle 23 Rue de Jardin FRANCE
SELECT * FROM BAD_ADDRESSES ORDER BY name$
REASON NAME NUMBER STREET COUNTRY
------ ------- ------ ----------- -------
Number Smith -1 Nowhere USA
Name - 38 Am Feldweg GERMANY |
The trigger could easily be adjusted to ignore the bad rows rather than storing them away.
Other applications of INSTEAD OF triggers might be:
- Avoiding recursive AFTER triggers.
Sometimes it happens that an AFTER trigger has to update its own subject table which, in extreme cases, can strain the available statement heap and compile time of the statement. Using a view over the subject table and creating an INSTEAD OF trigger allows multiple updates to the subject table without causing a recursive firing of the view. - Implementing external tables.
You might want to extend the existing read access to a table UDF in order to also have insert, update and delete capabilities. It is feasible to define insert, update and delete-specific UDFs. Then you can create a view over the table UDF and define INSTEAD OF triggers to drive the other UDFs. - Making WebSphere® MQ look like a table.
This would be similar to the external tables example above, but with WebSphere MQ functions.
While we tried many examples of using INSTEAD OF triggers, we have not talked about their limitations. Let's do that now.
Symmetric views
Due to the uncontrollable semantics attached to INSTEAD OF triggers, you cannot create an INSTEAD OF trigger on a symmetric view. A symmetric view is a view that accepts only rows that it can also return. Furthermore, no symmetric view can be created on top of a view that has an INSTEAD OF trigger.
Updatable cursors
INSTEAD OF triggers are currently not supported in the context of updatable cursors. This means a positioned update or positioned delete will fail if it causes an INSTEAD OF UPDATE or INSTEAD OF DELETE trigger, respectively, to fire.
Trigger firing order
INSTEAD OF triggers are considered to fire together with AFTER triggers. This means if an update or delete on a UNION ALL view causes INSTEAD OF triggers and/or AFTER triggers to fire, the complete set of triggers will fire in order of trigger creation time.
Trigger and constraint processing
As with AFTER triggers, SQL Statements in the body of an INSTEAD OF trigger are complete semantic entities. This means, for example, an UPDATE statement inside of an INSTEAD OF trigger will cause the respective checks, RI and triggers to fire before the next statement and ultimately the next row in the transition table gets processed.
GET_DIAGNOSTICS ROW_COUNT (a.k.a., SQLCA.ERRD(3))
Prior to DB2 V8, the number of rows modified through a view was always equal to the number of rows directly updated on a base table, where "directly" means excluding rows modified by triggers and delete cascade RI constraints. With INSTEAD OF triggers this is not true anymore, and the meaning of ROW_COUNT needs to be refined. ROW_COUNT in DB2 V8 specifies the number of rows that qualify for the update, delete or insert operation as given by the user. For an INSTEAD OF trigger on such an operation, this translates to the number of times the trigger is executed and to the cardinality of the transition tables.
INSTEAD OF triggers raise some interesting questions and options from an SQL language point of view. Examples are:
View-level defaults
Since INSTEAD OF triggers completely detach the semantics of a view's query from its behavior on insert, delete and update, it no longer seem sufficient to derive defaults only from the underlying tables. It seems prudent to consider views with explicit column defaults, possibly even identity column properties.
Statement-level INSTEAD OF triggers
While DB2 supports only row-level INSTEAD OF triggers today, there are various examples where a statement-level INSTEAD OF trigger might be beneficial. For example, the exception table trigger we discussed in one of the examples above would certainly perform better if processed once for a set of rows, rather than for each row.
Why only INSTEAD OF triggers on views?
Having introduced one kind of trigger to views, why stop there? Maybe BEFORE, AFTER triggers, and even IDENTITY are other candidates.
In this article we discussed the capabilities and limitations of updatable views. We introduced INSTEAD OF triggers, which allow you to turn any view into an updatable view, and discovered a variety of applications of this DB2 V8 for distributed platforms feature.
This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
Serge Rielau started his life in DB2 six years ago through a year-long internship at the Almaden Research Center where he worked on inline SQL PL and structured types. Since then he has been responsible for various SQL enhancements, including typed views and generated columns. As a technical manager for the SQL Query Compiler he now drives language changes into DB2 as well as the implementation of the SQL standard. He can be reached through comp.databases.ibm-db2 or at srielau@ca.ibm.com.
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
