Inserting Data through a View
Go Up to Creating Views (Data Definition Guide)
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
INSERT
privilege for the view. - The view is created using
WITH CHECK OPTION
.
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.
Examples
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;
The 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 DEPARTMENT
, DEPT_NO
, HEAD_DEPT
, and BUDGET
columns of the base table, DEPARTMENT
. 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 SUB_DEPT
view.
The following statement inserts a new row for the Publications Department through the SUB_DEPT
view:
INSERT INTO SUB_DEPT (DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET)
VALUES ('Publications', '7735', '670', 250000);
InterBase inserts NULL
values for all other columns in the DEPARTMENT
base table that are not available directly through the view.