SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Easy Introduction to CHECK Options – Day 24 of 35

Answer a simple quiz at the end of the blog post and -
Every day, one winner from India will get Joes 2 Pros Volume 4. Every day one winner from the United States will get Joes 2 Pros Volume 4.

Using Check Option

CHECK OPTION is a very handy tool we can use with our views. If I give you the definition right away and you don’t already know what it does, then is just confusing. However, the examples make perfect sense. So let’s save the definition for the end of this post. First, let’s look at the creation of the vHighValueGrants view.

CREATE VIEW vHighValueGrants
AS
SELECT GrantName, EmpID, Amount
FROM [Grant]
WHERE Amount > 20000

JProCo already offered to do a $1,000 match for each grant larger than $20,000. The $1,000 match is currently included in our vHighValueGrants amounts as seen in the figure below.

We have just been informed that the campaign didn’t receive the necessary approval from the stakeholders. Therefore, we must remove all of the $1,000 matching amounts. After we run this UPDATE statement, the grants will all be returned to their baseline values.

UPDATE vHighValueGrants
SET Amount = Amount - 1000

The large grants are decremented by $1,000 and thus returned to their baseline values. The $1000 increase has been removed as seen in this figure below:

Suppose you accidentally ran the decrement step twice. Before we “accidentally” run the UPDATE statement again in order to create this scenario, let’s consider the amounts currently shown by the view. The smallest grant in the vHighValueGrants view is $21,000. If we rerun the UPDATE statement, this grant will become $20,000. Recall that each grant must be greater than $20,000 in order to appear in the view.

Run the UPDATE statement again and then run a SELECT statement to see all of the records in the vHighValueGrants view. The $21,000 grant (contributed by Big 6’s Foundation %) was reduced to $20,000 and thus has fallen out of the view. Now that this grant has fallen outside the criteria of vHighValueGrants, the view no longer has the ability to “see” or manipulate this record using DML statements. For the five remaining grants, you can correct their amounts and reverse the “accidental” run of the UPDATE statement by incrementing each grant by $1000. However, the only way to correct the amount of the missing grant is by running an UPDATE statement directly against the Grant table. There are only 5 large grants remaining in the view after the second $1000 decrement.

This action was clearly a mistake. We didn’t intend to remove a record from the view, but as it is currently configured, vHighValueGrants isn’t protected against these kinds of mistakes. In order to prevent data updates which would cause records to disappear from our view, we can either place a trigger on the Grant table, or we can use CHECK OPTION.

CREATE TRIGGER trg_UpdateGrant
ON dbo.[Grant]
AFTER UPDATE
AS
BEGIN
IF EXISTS( SELECT * FROM Inserted ins
INNER JOIN Deleted del
ON ins.GrantName = del.GrantName
WHERE del.Amount > 20000
AND ins.Amount <= 20000)
ROLLBACK TRAN
END

If you create the trigger and then attempt to decrement the vHighValueGrants view, you’ll find that the trigger will not allow the transaction to fall to $20,000 and thus it won’t meet the criteria of the vHighValueGrants view.

The trigger has protected our view. The transaction which attempted to reduce a large grant from $21,000 to $20,000 was forbidden and ended in the trigger.

But let’s recognize that the trigger would also prevent any existing grant from ever being changed to an amount $20,000, or lower. In other words, the trigger is so restrictive that even a DBA would be disallowed from directly updating the Grant table if the change would reduce an existing grant amount to become $20,000 or lower. The trigger is more restrictive than we intended.

Our goal was simply to restrict users from making an accidental data change through the view which would result in a grant being removed from the view. Let’s reattempt our goal by using CHECK OPTION by rebuilding the vHighValueGrants view to include CHECK OPTION.

This tells the view to disallow data changes through the view which would cause any record to fall outside of the criteria of the view. Does it work? Now attempt to decrement though the view and the CHECK OPTION will block you. Using the code below, you get the following error message:

UPDATE vHighValueGrants
SET Amount = Amount – 1000

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.

You will however be allowed to update the table directly.

UPDATE [Grants]
SET Amount = Amount – 1000

OK as promised here is the short definition or description of what CHECK OPTION does for views. Each time a DML statement is run against the view, CHECK OPTION validates that the resulting record set will be true to the SELECT statement which built the view. If a modification would remove a record defined by the view, then CHECK OPTION prevents the transaction from being committed.

Note: If you want to setup the sample JProCo database on your system, you can watch this video. For this post, you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.

Question 23

You have a table named dbo.Sales. You need to create three views from the sales table.

vSalesSeattle
vSalesBoston
vSalesSpokane

Each view will be used by each region to make changes to their rows. One day, a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view. Which view should you create for Region1?

1. SQL

   CREATE VIEW dbo.vSalesSeattle
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1
   WITH DIFFERENTIAL

2. SQL

   CREATE VIEW dbo.vSalesSeattle
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1
   WITH CHECK OPTION

3. SQL

   CREATE VIEW dbo.vSalesSeattle
   WITH SCHEMABINDING
   AS
   SELECT SalesID,OrderQty,SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1

4. SQL

   CREATE VIEW dbo.vSalesSeattle
   WITH NOCHECK
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1

Rules

• Please leave your answer in the comment section below with the correct option, explanation and your country of residence.
• Every day, one winner will be announced from the United States.
• Every day, one winner will be announced from India.
• A valid answer must contain country of residence of the person who answers.
• Please check my Facebook page for winners' names and correct answer.
• Every day, one winner from India will get Joes 2 Pros Volume 4.
• Every day, one winner from United States will get Joes 2 Pros Volume 4.
• The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference: Pinal Dave (http://blog.SQLAuthority.com)