Применять бизнес-правила во время вставки SQL Server (проверить ограничение?)

У нас есть некоторые бизнес-правила, которые обеспечивают соблюдение таких вещей, как у Сотрудника может быть только одно текущее действие (Todo, In-Progress), а все остальные должны быть в другом состоянии (Завершено, Удалено).

Мы заметили, что иногда в базу данных вводятся повторяющиеся действия, хотя наши бизнес-правила должны это предотвращать (мы проверяем наличие активного действия перед вставкой). Мы думаем, что это небольшая проблема со временем (оба добавляются в одно и то же время из двух разных источников).

Можем ли мы использовать проверочное ограничение?

Правила бизнеса таковы:

  • Один сотрудник (Id) для каждого типа активности (ActivityId) с активным состоянием активности (0 или 1)

Таким образом, каждый из двух сотрудников может выполнять какое-либо действие и иметь его активным, или один сотрудник может иметь несколько одинаковых действий, если одновременно активен только один из них (другие могут находиться в состоянии 2/3).

  • EmployeeId 1 Действие 1 Состояние 1 существует в базе данных
  • добавление EmployeeId 1 Activity 1 State 0 не удастся
  • добавление EmployeeId 1 Activity 2 State 0 будет успешным
  • добавление EmployeeId 2 Activity 1 State 0 будет успешным

Как только существующая запись будет обновлена ​​до состояния 2 или 3, сотрудник снова сможет добавить новую запись в состояние todo/in progress.

Итак, можно ли поместить этот тип логики в ограничение проверки SQL Server или нам нужно использовать триггер или что-то постфактум, чтобы удалить эти «дубликаты»?

Редактировать:

Вот функция, которая у меня есть, но она выдает ошибку во всем, что я пытаюсь вставить (верно возвращает 1 или 0 при запуске за пределами ограничения):

Мне пришлось изменить функцию, чтобы принять идентификатор для игнорирования, поскольку проверочное ограничение не работало, потому что оно нашло вставляемую запись!

ALTER FUNCTION [dbo].[ActiveEmployeeActivitiyExists] 
(
    -- Add the parameters for the function here
    @ActivityId nvarchar(5),
    @EmployeeId int,
    @IgnoreId int,
)
RETURNS int
AS
BEGIN
  DECLARE @Id int

  SELECT Top(1) @Id = Id
  FROM [dbo].[EmployeeActivities]
  WHERE EmployeeId = @EmployeeId and ActivityId = @ActivityId and ActivityState < 2 and Id <> @IgnoreId

  RETURN CASE WHEN(@Id IS NULL) THEN 0 ELSE 1 END
END

sql-server tsql check-constraints
person John    schedule 11.12.2014    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете использовать Check Constraint или триггер. Недостатком проверочного ограничения является то, что если индекс нарушает ограничение, возникает ошибка. Другого варианта обработки в ограничении нет. Вместо этого вам придется обрабатывать ошибку в коде, выполняющем вставку.

Если вы решите использовать проверочное ограничение, лучшим вариантом для его реализации, вероятно, будет написать udf, который проверяет ваши бизнес-правила и возвращает простое прохождение/непрохождение (1 или 0), а ограничение просто проверяет dbo.MyFunction(SomeColumn or Columns)=1

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

Если у вас есть два столбца с именами EmployeeId и ActivityId, и вы хотите передать их в качестве параметров в функцию ограничения проверки, тогда ваше ограничение проверки будет таким:

dbo.MyFunction(EmployeeId, ActivityId) = 1

Поскольку ограничение проверки записывает вставку до ее запуска, вам нужно будет игнорировать идентификатор и также передать его вашей функции:

dbo.MyFunction(EmployeeId, ActivityId, Id) = 1
person Tab Alleman    schedule 11.12.2014
comment
Как бы я написал функцию, чтобы знать, чтобы проверить эти поля? Я написал быструю функцию, которая делает выбор и требует некоторые параметры (EmployeeId и ActivityId), а затем возвращает 1 или 0, если они существуют, но как мне вызвать это из моего контрольного ограничения и передать ему вставляемые значения? - person John; 12.12.2014
comment
На самом деле проблема была вызвана тем, что вставка происходит до/во время ограничения проверки, поэтому мне пришлось игнорировать идентификатор, который я вставлял из моего запроса на выборку (иначе он увидит его и потерпит неудачу!) - person John; 12.12.2014