top of page

With Check Option

When using a view as an interface like we are doing in this section, one of the things that you generally don’t want to occur is to have a DML statement affect the view of the data that is not visible to the user of the view, as we saw in the previous section.

In order to stop this from occurring, there is a clause on the creation of the view called WITH CHECK OPTION that checks to make sure that the result of the INSERT or UPDATE statement is still visible to the user of the view.

 

The basic form of a view is very straightforward:

CREATE VIEW SchemaName.ViewName [WITH OPTIONS] AS SELECT .. FROM .. WHERE

[WITH CHECK OPTION]

THE WITH CHECK OPTION limits what can be modified in the VIEW to what could be returned by the VIEW object.

 

http://ingenieriacognitiva.com/developer/cursos/OracleDeveloper/chapters/c11.php

 

For example:

ALTER VIEW Examples.ElectronicGadget AS SELECT GadgetId, GadgetNumber, GadgetType, UPPER(GadgetType) AS UpperGadgetType FROM Examples.Gadget WHERE GadgetType = 'Electronic'

WITH CHECK OPTION;

Now, when you attempt to create a new row that would not be visible, you get an error. As an example, try the following:

INSERT INTO Examples.ElectronicGadget(GadgetId, GadgetNumber, GadgetType) VALUES (6,'00000006','Manual');

This returns the following error now:

Msg 550, Level 16, State 1, Line 482 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

 

Question:

You run the following Transact-SQL statements:

Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created. You need to ensure that you can insert rows into the Orders table by using the view.

  • A. Add the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.

  • B. Create an INSTEAD OF trigger on the view.

  • C. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.

  • D. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

bottom of page