Inserting Rows Through a View

You can insert data through a single-table view if you have the Insert privilege on the view. To do this, the defining SELECT statement can select from only one table, and it cannot contain any of the following components:

• DISTINCT keyword
• GROUP BY clause
• Derived value (also referred to as a virtual column)
• Aggregate value

Columns in the underlying table that are unspecified in the view receive either a default value or a NULL value if no default is specified. If one of these columns has no default value, and a NULL value is not allowed, the INSERT fails.

You can use data-integrity constraints to prevent users from inserting values into the underlying table that do not fit the view-defining SELECT statement. For further information, see WITH CHECK OPTION Keywords.

With Dynamic Server, you can insert rows through a single-table or a multiple-table view if an INSTEAD OF trigger specifies valid INSERT operations in its Action clause. See INSTEAD OF Triggers on Views (IDS) for information on how to create INSTEAD OF triggers that insert through views.

If several users are entering sensitive information into a single table, the built-in USER function can limit their view to only the specific rows that each user inserted. The following example contains a view and an INSERT statement that achieves this effect:

CREATE VIEW salary_view AS
   SELECT lname, fname, current_salary FROM salary WHERE entered_by = USER
 
INSERT INTO salary VALUES ('Smith', 'Pat', 75000, USER)