Inserting Rows Through a View
- 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.
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 for information on how to create INSTEAD OF triggers that insert through views.
CREATE VIEW salary_view AS SELECT lname, fname, current_salary FROM salary WHERE entered_by = USER; INSERT INTO salary VALUES ('Smith', 'Pat', 75000, USER);