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.

aboutME

I am John Fan Zhang, a data analyst and finance researcher. I hold a PhD in finance, CFA charter and full membership of CFA New Zealand Society. I have fifteen-year experience in corporate investment and eight-year experience in advanced data analysis. My research focuses on the effect of social psychology (culture) on financial decisions. Finance research involves heaps of data analyses that lead me to the data field. I am a Microsoft Certified Solutions Expert (MCSE): Data Management and Analytics (Excel, Power BI, and SQL). Aside from Excel, Power BI and SQL, I am also familiar with econometric tools such as Stata, Eviews, and MATLAB. I use OX and Python for programming. I am an active data community event participant, volunteer, speaker, moderator, program reviewer, including PASS Marathon 2020, Global AI BootCamp Auckland 2019, SQL Saturday Auckland (2017, 2018, 2019), and Definity Conference (2018, 2019, 2020, Auckland, New Zealand).

Auckland, New Zealand

  • Google Site
  • Twitter
  • LinkedIn

©2016 BY JOHN'S DATA STORY

bottom of page