'WITH CHECK OPTION' Forces all data modification statements executed against the view to follow the criteria set within select_statement.
When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Example:
CREATE VIEW CheckOptionTest
AS
SELECT [StamTableId], [StamTableStr], [colIntA]
FROM StamTable
WHERE [colIntA] > 5
GO
-- select #1:
select * from CheckOptionTest
GO
-- update #1:
update CheckOptionTest set [colIntA] = [colIntA] - 2
GO
-- select #2:
select * from CheckOptionTest
-- less rows than select #1 (because of the update)
GO
ALTER VIEW CheckOptionTest
AS
SELECT [StamTableId], [StamTableStr], [colIntA]
FROM StamTable
WHERE [colIntA] > 5
WITH CHECK OPTION
GO
-- update #2:
update CheckOptionTest set [colIntA] = [colIntA] - 2
GO
-- in case of rows that colIntA is not bigger from 5 in more than 2:
-- update will be failed: can't update rows from the view with values that don't follow the criteria set within the select statement.
No comments:
Post a Comment