View with check option : View « View « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. View
3. View

5> CREATE TABLE Shippers (
6>      ShipperID int NOT NULL ,
7>      CompanyName nvarchar (40) NOT NULL ,
8>      Phone nvarchar (24) NULL
9> )
10> GO
1>
2>
3> INSERT Shippers VALUES(1,'Express','(503) 555-9831')
4> INSERT Shippers VALUES(2,'Package','(503) 555-3199')
5> INSERT Shippers VALUES(3,'Shipping','(503) 555-9931')
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>    CREATE VIEW OregonShippers_vw
3>    AS
4>    SELECT   ShipperID,
5>             CompanyName,
6>             Phone
7>    FROM     Shippers
8>    WHERE Phone LIKE '(503)%'
9>    WITH CHECK OPTION
10> GO
1>
2>
3>
4>    UPDATE OregonShippers_vw
5>    SET Phone = '(333) 555 9831'
6>    WHERE ShipperID = 1
7> GO
Msg 550, Level 16, State 1, Server J\SQLEXPRESS, Line 4
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.
1> drop view OregonShippers_vw;
2> GO
1>
2> drop table Shippers;
3> GO