WITH CHECK OPTION in SQL Server: Stop Rows Leaving a View

WITH CHECK OPTION stops a change made through a view from pushing a row out of that view. Without it, an update can make a row vanish from the view while it stays in the table. This post shows how that happens, how the option prevents it, and why a trigger is the heavier tool.

The Sample View

Invoices holds seven invoices. The view vLargeInvoices shows only those over 2,000, and it has no CHECK OPTION yet. You can edit a simple view like this one as if it were the table. SQL Server changes the matching table rows. Run this in any test database. Every result below comes from SQL Server 2025.

CREATE TABLE Invoices (InvoiceID INT NOT NULL PRIMARY KEY, Customer VARCHAR(20) NOT NULL, Amount DECIMAL(10,2) NOT NULL);
GO

INSERT INTO Invoices (InvoiceID, Customer, Amount) VALUES
  (1, 'Arbor Books', 2200), (2, 'Blue Door Cafe', 2350), (3, 'Cedar Garden', 2600), (4, 'Dune Kites', 3100),
  (5, 'Eastside Tea', 5000), (6, 'Fig Studio', 800), (7, 'Grove Cycles', 1200);
GO

CREATE VIEW dbo.vLargeInvoices
AS
SELECT InvoiceID, Customer, Amount
FROM Invoices
WHERE Amount > 2000;
GO

Query the view first. It returns five invoices, from 2,200 up to 5,000.

SELECT InvoiceID, Customer, Amount FROM dbo.vLargeInvoices ORDER BY InvoiceID;

The Row That Vanishes

Suppose a fee of 150 was added to every large invoice by mistake. You remove it with an update through the view. All five invoices stay in the view, now between 2,050 and 4,850.

UPDATE dbo.vLargeInvoices SET Amount = Amount - 150;

Now suppose someone runs the same update a second time. Invoice 1 drops from 2,050 to 1,900, which is no longer over 2,000. It leaves the view, and the view returns four rows. The table still holds the row, so you can fix it only with an update against the table.

SELECT InvoiceID, Customer, Amount FROM Invoices ORDER BY InvoiceID;

Inserts Can Vanish Too

The same gap exists for new rows. This insert through the view adds an invoice of 500. SQL Server accepts it, but the view can’t show it. The table now has eight rows, and the view still has four.

INSERT INTO dbo.vLargeInvoices (InvoiceID, Customer, Amount) VALUES (8, 'Hill Spices', 500);

Add WITH CHECK OPTION

Undo the damage by adding 300 back to the five large invoices and deleting the invoice that was inserted. Then rebuild the view with the option on the last line.

UPDATE Invoices SET Amount = Amount + 300 WHERE InvoiceID IN (1, 2, 3, 4, 5);
DELETE FROM Invoices WHERE InvoiceID = 8;

ALTER VIEW dbo.vLargeInvoices
AS
SELECT InvoiceID, Customer, Amount
FROM Invoices
WHERE Amount > 2000
WITH CHECK OPTION;

The option tells SQL Server to check every change made through the view. If the changed row would fall outside the WHERE clause, the change is rejected. The first update works, because every row stays over 2,000.

UPDATE dbo.vLargeInvoices SET Amount = Amount - 150;

The second run fails with Msg 550, which says the row didn’t qualify under the CHECK OPTION constraint. The statement ends, and no row changes. The insert of 500 through the view fails the same way.

UPDATE dbo.vLargeInvoices SET Amount = Amount - 150;

INSERT INTO dbo.vLargeInvoices (InvoiceID, Customer, Amount) VALUES (8, 'Hill Spices', 500);

The option guards the view only. A direct update against the table is still allowed, so an administrator can still set invoice 1 to 1,900.

UPDATE Invoices SET Amount = Amount - 150 WHERE InvoiceID = 1;

Why Not Use a Trigger?

A trigger can do a similar job. This one rolls back any update that takes an invoice from over 2,000 to 2,000 or less.

CREATE TRIGGER trg_Invoices_Floor
ON dbo.Invoices
AFTER UPDATE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted AS i JOIN deleted AS d ON d.InvoiceID = i.InvoiceID WHERE d.Amount > 2000 AND i.Amount <= 2000)
    ROLLBACK TRANSACTION;
END;

It protects the view, but it is too strict. A direct update on the table is blocked as well, with Msg 3609: the transaction ended in the trigger. WITH CHECK OPTION is narrower, because it guards only changes made through the view.

UPDATE Invoices SET Amount = 1900 WHERE InvoiceID = 5;

Drop the trigger before you go on.

DROP TRIGGER trg_Invoices_Floor;

Clean Up

Remove the view and the table when you finish.

DROP VIEW dbo.vLargeInvoices;
DROP TABLE Invoices;

What to Remember

Add WITH CHECK OPTION to a view that filters rows when people edit through it. A view per region is the classic case. The option stops a user from changing a row so that it belongs to another region’s view. A trigger can do more, but it also blocks changes you meant to allow.

To go further, read Updatable Views: When INSERT and UPDATE Work Through a View, What Is a View in SQL Server? and Schema-Bound Views: Protect Referenced Columns.

WITH CHECK OPTION is not a filter on the view, it is a guard on every change made through it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.