WITH CHECK OPTION

Creating a View without "WITH CHECK OPTION":

        We created a view with filter condition. If it is a Simple view then we can perform DML operations.

Example:

Employee table script

View creation script:

CREATE VIEW VW_EMPLOYEE2

AS

SELECT

ID

,FirstName

,LastName

,Gender

,Designation

,ManagerID

,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10

View output:

The view will display department 10 data only, because view is created with where condition.

 

    

Inserting data into 'VW_EMPLOYEE2':

I am going to insert two records one is with department id 10 other one is 20.

Insert stmt:

INSERT INTO VW_EMPLOYEE2

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (112,'A','Z','M',NULL,NULL,10),

(113,'B','Y','F',NULL,NULL,20)

View output:

We can see only 10th department data using view, because view is having where condition. We can see the newly inserted records in the table.

 

Table output:

Creating a View with "WITH CHECK OPTION":

        We created a view with filter condition and 'WITH CHECK OPTION'. If it is a Simple view then we can  insert the data. The record must satisfied the filter condition, other wise it will throw error.

Example:

View creation script:

CREATE VIEW VW_EMPLOYEE3

AS

SELECT

                ID

                ,FirstName

                ,LastName

                ,Gender

                ,Designation

                ,ManagerID

                ,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10 

WITH CHECK OPTION

Insert stmt:

INSERT INTO VW_EMPLOYEE3

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (113,'B','Y','F',NULL,NULL,20) 

Error msg:

                                                Msg 550, Level 16, State 1, Line 1

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.

The statement has been terminated.