I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails.

Suppose you need to move some stuff in your car, and you don't want to load more than 100 pounds into your small car at a time. Here is the DDL:

CREATE SCHEMA Data AUTHORIZATION dbo;
GO
CREATE SCHEMA Readers AUTHORIZATION dbo;
GO
CREATE TABLE Data.ItemsToMove(
  TripNumber INT NOT NULL,
  ItemName VARCHAR(50),
  ItemWeightInPounds FLOAT
);
GO
CREATE FUNCTION Readers.TotalWeightPerTrip(@TripNumber INT)
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  WHERE TripNumber = @TripNumber);
END
GO
ALTER TABLE Data.ItemsToMove
  ADD CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds
  CHECK (Readers.TotalWeightPerTrip(TripNumber) <= 100);
GO
  

Loading 90 pounds succeeds, but when you go over the limit, the constraint works:

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 1, 'Big Box with Clothes', 40
UNION ALL
SELECT 1, 'Lumbar Aeron Chair', 25
UNION ALL
SELECT 1, 'Basic Aeron Chair', 25;

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 1, 'Small Box with Books', 40;
  

 Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

So, all other items should be moved on the second trip:

INSERT INTO Data.ItemsToMove(TripNumber,
  ItemName,
  ItemWeightInPounds)
SELECT 2, 'Small Box with Books', 40
UNION ALL
SELECT 2, 'Humidifier', 40;
  

Now there is another problem: all bulky items are scheduled to move on the first trip, while small and hevy ones get on the second trip. Suppose you cannot fit the bulky box with clothes after you loaded your chairs, and want to move your small humidifier on your first trip instead:

UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName IN ('Humidifier', 'Big Box with Clothes') AND TripNumber IN(1,2);  

The update should succeed, because the box and the humidifier have the same weight. However, the update fails:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

The reason is simple: the database engine modifies just one row and immediately invokes the CHECK constraint for that row. At that moment the limit for total load is exceeded no matter which row is modified first - that is the way I made up this example. You can run the following scripts and see for yourself:

ALTER TABLE Data.ItemsToMove
  DROP CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds;

BEGIN TRAN


UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  ELSE TripNumber END
WHERE  ItemName = 'Humidifier' AND TripNumber = 2;


SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;


ROLLBACK;

BEGIN TRAN


UPDATE Data.ItemsToMove 
SET TripNumber = CASE 
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName = 'Big Box with Clothes' AND TripNumber = 1;


SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;


ROLLBACK;
  

 At this point the faulty constraint is dropped, so you can rerun your multi-row update:

UPDATE Data.ItemsToMove 
SET TripNumber = CASE WHEN ItemName = 'Humidifier' THEN 1
  WHEN ItemName = 'Big Box with Clothes' THEN 2
  ELSE TripNumber END
WHERE  ItemName IN ('Humidifier', 'Big Box with Clothes') AND TripNumber IN(1,2);   

 Create the constraint again, it will succeed. Verify that the constraint is trusted:

SELECT name, is_not_trusted
FROM   sys.check_constraints
WHERE Name='CHK_TotalWeightPerTrip_LessThan100pounds';
 

You can also verify that your data is clean, that the load for every trip does not exceed 100 pounds:

 SELECT TripNumber, SUM(ItemWeightInPounds) 
  FROM Data.ItemsToMove
  GROUP BY TripNumber;