Inserting Data Through a View (Embedded SQL Guide)
Go Up to Inserting Data (Embedded SQL Guide)
New rows can be inserted through a view if the following conditions are met:
- The view is updatable. For a complete discussion of updatable views, see the Data Definition Guide.
- The view is created using the
WITH CHECK OPTION. - A user or stored procedure has
INSERTprivilege for the view.
Values can only be inserted through a view for those columns named in the view. InterBase stores NULL values for unreferenced columns. For example, suppose the view, PART_DEPT, is defined as follows:
EXEC SQL CREATE VIEW PART_DEPT (DEPARTMENT, DEPT_NO, BUDGET) AS SELECT DEPARTMENT, DEPT_NO, BUDGET FROM DEPARTMENT WHERE DEPT_NO NOT NULL AND BUDGET > 50000 WITH CHECK OPTION;
Because PART_DEPT references a single table, DEPARTMENT, new data can be inserted for the DEPARTMENT, DEPT_NO, and BUDGET columns. The WITH CHECK OPTION assures that all values entered through the view fall within ranges of values that can be selected by this view. For example, the following statement inserts a new row for the Publications department through the PART_DEPT view:
EXEC SQL
INSERT INTO PART_DEPT (DEPARTMENT, DEPT_NO, BUDGET)
VALUES ('Publications', '7735', 1500000);
InterBase inserts NULL values for all other columns in the DEPARTMENT table that are not available directly through the view.
For information about creating a view, see Working with Data Definition Statements. For the complete syntax of CREATE VIEW, see the Language Reference Guide.
See “Working with Triggers” in the Data Definition Guide for tips on using triggers to update non-updatable views.