Проверочное ограничение уровня таблицы

В описании схемы «Окраска» утверждается, что объем краски одного цвета на квадрате не может превышать 255 единиц. Как реализовать это ограничение? Рассмотренные ранее варианты нам не подойдут, т.к. каждая строка таблицы utB может отвечать всем ограничениям на отдельную окраску, но суммарный объем при этом может превысить допустимый предел. Ограничение подобного типа называется ограничением уровня таблицы, т.е. при проверке оно адресуется не к отдельной строке, которой коснулось изменение, а ко всей таблице.

Поскольку тут нам опять потребуется запрос в ограничении CHECK, что не допускается реализацией, напишем сначала пользовательскую функцию, которая будет возвращать 1, если объем какой-либо краски на каком-либо квадрате превысил 255 единиц, и ноль – в противном случае. Лежащий в основе UDF запрос достаточно прост – группировка по ИД квадрата и цвету с фильтрацией в предложении HAVING по условию, что сумма краски превысила 255. Если такой запрос будет содержать строки, то функция вернет 1, иначе – 0. Собственно функция:

CREATE FUNCTION check_volume()
RETURNS INT
AS
BEGIN
DECLARE @ret int
if EXISTS(SELECT sum(B_VOL)
          FROM utB 
            join utV on b_v_id=v_id
          group by b_q_id, V_COLOR
          HAVING sum(B_VOL) > 255
         )
SELECT @ret =1 else SELECT @ret = 0;
RETURN @ret;
END;

Осталось написать совсем простое ограничение – возвращаемое функцией значение равно 0 или не равно 1, - это кому как нравится:

Alter table utB
    ADD CONSTRAINT square_volume check(dbo.check_volume() = 0);

Попробуем теперь добавить какой-нибудь краски к белому квадрату (т.е. квадрату, который уже окрашен по максимуму всеми цветами), например, квадрату с b_q_id = 1:

insert into utB values(CURRENT_TIMESTAMP, 1, 4, 10);

В результате мы получим ошибку:

(Конфликт инструкции INSERT с ограничением CHECK “square_volume”. Конфликт произошел в базе данных “learn”, таблица “dbo.utb”. Выполнение данной инструкции было прервано.)

В качестве упражнения напишите ограничение, которое запретит использование пустых баллончиков, т.е. когда объем краски, израсходованной из баллончика, оказывается более 255.