Inserting Data through a View

From InterBase
Jump to: navigation, search

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.
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.

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.

Advance To: