Inserting Data through a View
Rows can be inserted and updated through a view if the following conditions are met:
- The view is update-able.
- A user or stored procedure has
INSERTprivilege for the view.
- The view is created using
WITH CHECK OPTION.
- Tip: You can simulate updating a read-only view by writing triggers that perform the appropriate writes to the underlying tables. For an example of this, see Updating Views with Triggers.
Using WITH CHECK OPTION
WITH CHECK OPTION specifies rules for modifying data through views. This option can be included only if the views are update-able. Views that are created using
WITH CHECK OPTION enable InterBase to verify that a row inserted or updated through a view can be seen through the view before allowing the operation to succeed. Values can only be inserted through a view for those columns named in the view. InterBase stores
NULL values for un-referenced columns.
WITH CHECK OPTION prevents you from inserting or updating values that do not satisfy the search condition specified in the
WHERE clause of the
SELECT portion of the
CREATE VIEW statement.
Suppose you want to create a view that allows access to information about all departments with budgets between $10,000 and $500,000. The view,
SUB_DEPT, is defined as follows:
CREATE VIEW SUB_DEPT (DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET) AS SELECT DEPARTMENT, DEPT_NO, HEAD_DEPT, BUDGET FROM DEPARTMENT WHERE BUDGET BETWEEN 10000 AND 500000 WITH CHECK OPTION;
SUB_DEPT view references a single table,
DEPARTMENT. If you are the creator of the view or have
INSERT privileges, you can insert new data into the
BUDGET columns of the base table,
WITH CHECK OPTION assures that all values entered through the view fall within the range prescribed for each column in the
WHERE clause of the
The following statement inserts a new row for the Publications Department through the
INSERT INTO SUB_DEPT (DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET) VALUES ('Publications', '7735', '670', 250000);
NULL values for all other columns in the
DEPARTMENT base table that are not available directly through the view.