You have an application that uses a view to access data from multiple tables. You need to ensure that you can insert rows into the underlying tables by using the view. What should you do?

The correct answer is C. Create an INSTEAD OF trigger on the view. SQL Server does not allow a single DML INSERT statement to directly modify multiple base tables through a view. An INSTEAD OF trigger solves this by intercepting the INSERT (or UPDATE/DELETE) statement before it executes and replacing it with custom T-SQL logic that correctly…

Role of Transact-SQL and the Database Engine in Enterprise IT

Question

Options

ADefine the view by using the SCHEMABINDING option.
BDefine the view by using the CHECK option.
CCreate an INSTEAD OF trigger on the view.
DMaterialize the view.

How the community answered

(27 responses)

A
4% (1)
C
89% (24)
D
7% (2)

Explanation

SQL Server does not allow a single DML INSERT statement to directly modify multiple base tables through a view. An INSTEAD OF trigger solves this by intercepting the INSERT (or UPDATE/DELETE) statement before it executes and replacing it with custom T-SQL logic that correctly distributes the incoming row data across the appropriate underlying tables. The trigger fires 'instead of' the original DML action, giving you full control over how each table is populated. The other options do not solve the problem: (A) SCHEMABINDING prevents schema changes to the underlying objects but does not enable multi-table inserts; (B) WITH CHECK OPTION enforces WHERE-clause filter conditions on DML but cannot split data across tables; (D) materializing (indexing) a view improves query performance but still does not allow inserts into multiple underlying tables.

Topics

#views#INSTEAD OF trigger#DML operations#T-SQL

Community Discussion

No community discussion yet for this question.

Full 70-467 Practice