Google+ Followers

Tuesday, August 5, 2014

WITH CHECK OPTION in View

I have already discussed about Views in my previous post. In this article we will know the use of WITH CHECK OPTION on Views.

When we create a view without CHECK Option, in that case we can perform INSERT, UPDATE, DELETE operation on View. If records are not in scope of View and we want to perform DELETE and UPDATE operation on those records then DELETE and UPDATE operation will be succeed without any modification in the base table but INSERT operation will succeed and it will insert new records in base table.

When we create a view WITH CHECK OPTION, in that case we can’t perform INSERT, UPDATE, DELETE operation on those records which are not satisfying the criteria set within the select statement of View. If we try to perform DELETE and UPDATE operation on out of scope records then the operation will be succeed without any modification in the base table.

Let’s go through a simple example

USE TEACHMESQLSERVER
GO
-- Creating a new table and popluating data into the table
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP( EMPID INT, EMPNAME VARCHAR(MAX))
GO
INSERT INTO EMP VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')
GO

-- Creating a View without CHECK Option
IF OBJECT_ID('EMPVIEW','V') IS NOT NULL
DROP VIEW EMPVIEW
GO
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
GO
SELECT * FROM EMPVIEW

--Performing DML Operation on VIEW
GO
INSERT INTO EMPVIEW VALUES(6,'F') -- Successfully Inserted new record in Base Table
GO
SELECT * FROM EMP
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
SELECT * FROM EMP

WITH CHECK OPTION EXAMPLE

-- ALTERING the View WITH CHECK OPTION
GO
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
WITH CHECK OPTION
--Performing DML Operation on VIEW
GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5
GO
INSERT INTO EMPVIEW VALUES(7,'H') -- Failed with below error, because the INSERT statement is not satisfying the Cretria set in Select Statement, VIEW can have all records where EMPID<5 in this case EMPID is greater than 5 so it failed.

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.

GO
INSERT INTO EMPVIEW VALUES(0,'I') -- Successfully Inserted new record in Base Table because it satisfied EMPVIEW Criteria.
GO

SELECT * FROM EMP

No comments: