Browse Prior Art Database

Symmetric Views

IP.com Disclosure Number: IPCOM000047927D
Original Publication Date: 1983-Dec-01
Included in the Prior Art Database: 2005-Feb-08
Document File: 1 page(s) / 12K

Publishing Venue

IBM

Related People

Baker, JW: AUTHOR [+4]

Abstract

The symmetric view procedure herein described assures that when an INSERT or UPDATE statement is executed against a relational data base view, that a subsequent SELECT from the view will reflect back the INSERT or UPDATE, or else the INSERT or UPDATE change is not allowed. Consider the view defined by the following IBM Structured Query Language (SQL) statement: CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > The INSERT statement "INSERT INTO V1 VALUES (970)" will cause a row containing the value 970 to be inserted into the base table, T1, and the user will be given a return code indicating normal completion, but a subsequent "SELECT * FROM V1" statement will not return the inserted row. Further, the UPDATE statement "UPDATE V1 SET COL1 = COL1 - 100" may cause the value of COL1 in some rows to drop below 1000.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 90% of the total text.

Page 1 of 1

Symmetric Views

The symmetric view procedure herein described assures that when an INSERT or UPDATE statement is executed against a relational data base view, that a subsequent SELECT from the view will reflect back the INSERT or UPDATE, or else the INSERT or UPDATE change is not allowed. Consider the view defined by the following IBM Structured Query Language (SQL) statement: CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > The INSERT statement "INSERT INTO V1 VALUES (970)" will cause a row containing the value 970 to be inserted into the base table, T1, and the user will be given a return code indicating normal completion, but a subsequent "SELECT * FROM V1" statement will not return the inserted row. Further, the UPDATE statement "UPDATE V1 SET COL1 = COL1 - 100" may cause the value of COL1 in some rows to drop below 1000. Therefore, an unknown number of rows may be virtually deleted from the view. Described here is a CHECK OPTION which may be added to the CREATE VIEW statement to allow the user to specify that INSERTs and UPDATEs against the view should be checked to ensure that all resultant rows satisfy the view definition. When an INSERT or UPDATE is issued against a view with the CHECK option, a savepoint is taken (even if it is a single row operation). After each row is modified (INSERTed or UPDATEd), the cursor is positioned at that row and a fetch is issued against the view. If the row that is fetched is not the same row that was just modified, that...