View Retrieval, INSERT Statement and a View

A view is used exactly like any base table of a database. You can think of selecting from a view as if the statement were transformed into an equivalent operation on the underlying base table(s). Example 11.9 shows this.

Example 11.9

USE sample;
GO
CREATE VIEW v_d2
	AS SELECT emp_no, emp_lname
		FROM employee
		WHERE dept_no ='d2';
GO
SELECT emp_lname
	FROM v_d2
	WHERE emp_lname LIKE 'J%';Code language: PHP (php)

The result is:

The SELECT statement in Example 11.9 is transformed into the following equivalent form, using the underlying table of the v_d2 view:

SELECT emp_lname
	FROM employee
	WHERE emp_lname LIKE 'J%'
	AND dept_no ='d2';Code language: JavaScript (javascript)

The next three sections describe the use of views with the other three DML statements: INSERT, UPDATE, and DELETE. Data modification with these statements is treated in a manner similar to a retrieval. The only difference is that there are some restrictions on a view used for insertion, modification, and deletion of data from the table that it depends on.

INSERT Statement and a View

A view can be used with the INSERT statement as if it were a base table. When a view is used to insert rows, the rows are actually inserted into the underlying base table.

The v_dept view, which is created in Example 11.10, contains the first two columns of the department table. The subsequent INSERT statement inserts the row into the underlying table using the values ‘d4’ and ‘Development’. The location column, which is not referenced by the v_dept view, is assigned a NULL value.

Example 11.10

USE sample;
GO
CREATE VIEW v_dept
	AS SELECT dept_no, dept_name
		FROM department;
GO

INSERT INTO v_dept
	VALUES('d4', 'Development');Code language: PHP (php)

Using a view, it is generally possible to insert a row that does not satisfy the conditions of the view query’s WHERE clause. The option WITH CHECK OPTION is used to restrict the insertion of only such rows that satisfy the conditions of the query. If this option is used, the Database Engine tests every inserted row to ensure that the conditions in the WHERE clause are evaluated to TRUE. If this option is omitted, there is no check of conditions in the WHERE clause, and therefore every row is inserted into the underlying table. This could lead to the confusing situation of a row being inserted using a view but subsequently not being returned by a SELECT statement against that view, because the WHERE clause is enforced for the SELECT. WITH CHECK OPTION is also applied to the UPDATE statement.

Examples 11.11 and 11.12 show the difference of applying and not applying WITH CHECK OPTION, respectively.

Example 11.11

USE sample;
GO
CREATE VIEW v_2006_check
	AS SELECT emp_no, project_no, enter_date
		FROM works_on
		WHERE enter_date BETWEEN '01.01.2006' AND '12.31.2006'
		WITH CHECK OPTION;
GO

INSERT INTO v_2006_check
	VALUES (22334, 'p2', '1.15.2007');Code language: PHP (php)

In Example 11.11, the system tests whether the inserted value of the enter_date column evaluates to TRUE for the condition in the WHERE clause of the SELECT statement. The attempted insert fails because the condition is not met.

Example 11.12

USE sample;
GO
CREATE VIEW v_2006_nocheck
	AS SELECT emp_no, project_no, enter_date
		FROM works_on
		WHERE enter_date BETWEEN '01.01.2006' AND '12.31.2006';
GO

INSERT INTO v_2006_nocheck
	VALUES (22334, 'p2', '1.15.2007');
SELECT *
	FROM v_2006_nocheck;Code language: PHP (php)

The result is

Because Example 11.12 does not use WITH CHECK OPTION, the INSERT statement is executed and the row is inserted into the underlying works_on table. Notice that the subsequent SELECT statement does not display the inserted row because it cannot be retrieved using the v_2006_nocheck view.

The insertion of rows into the underlying tables is not possible if the corresponding view contains any of the following features:

The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table
A column of the view is derived from an aggregate function
The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option
A column of the view is derived from a constant or an expression

Example 11.13 shows a view that cannot be used to insert rows in the underlying base table.

Example 11.13

USE sample;
GO
CREATE VIEW v_sum(sum_of_budget)
	AS SELECT SUM(budget)
		FROM project;
GO

SELECT *
	FROM v_sum;Code language: PHP (php)

Example 11.13 creates the v_sum view, which contains an aggregate function in its SELECT statement. Because the view in the example represents the result of an aggregation of many rows (and not a single row of the project table), it does not make sense to try to insert a row into the underlying table using this view.

INSERT Statement and a View

Related Lessons: