Enforce Business Rules during SQL Server Insert (Check Constraint?)

Question

We have some business rules that enforce things such as Employee's can only have one current activity (Todo, In-Progress), and all others must be a different State (Completed, Removed)

We've noticed that sometimes we get duplicate activities entered into the database even though our business rules should prevent it (we check for an active one before inserting). We think its a small timing issue (both are adding at the exact same time from two different sources)

Would we be able to use a check constraint?

The business rules are:

One Employee (Id) per activity type (ActivityId) with activity state that is active (0 or 1)

So two employees can each be doing an activity and have it active, or a single employee can have multiple of the same activity as long as only one of them is active at a time (others can be in state 2 / 3)

EmployeeId 1 Activity 1 State 1 exists in database
adding EmployeeId 1 Activity 1 State 0 would fail
adding EmployeeId 1 Activity 2 State 0 would succeed
adding EmployeeId 2 Activity 1 State 0 would succeed

Once the existing record gets updated to state 2 or 3 then the employee would again be able to add a new record in todo/in progress state.

So can this type of logic be placed in a SQL Server check constraint or do we need to use a trigger or something after the fact to remove these "duplicates"?

Edit:

Here is the function I have but its erroring on everything I try to insert (returns 1 or 0 correctly when run outside of the constraint):

I had to alter the function to accept an Id to ignore, as the check constraint was failing because it found the record it was inserting!

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

score 0 · Accepted Answer · edited Dec 12 '14 at 17:59

You can either use a Check Constraint or a trigger. The disadvantage of a check constraint is that if the index violates the constraint, it will raise an error. There is no other option for handling it in a constraint. You'd have to handle the error instead in the code that performs the insert.

If you choose to use a check constraint, your best option for implementing it is probably to write a udf that tests for your business rules and returns a simple pass/fail (1 or 0), and have the constraint simply test dbo.MyFunction(SomeColumn or Columns)=1

In a trigger, you have the option to write code to handle what you want done if an insert violates your business rules. But of course, triggers create other complications which are already well-documented on the interwebz.

If you have two columns named EmployeeId and ActivityId, and want to pass them as parameters in your check constraint function, then your check constraint would be:

dbo.MyFunction(EmployeeId, ActivityId) = 1

Because the check constraint writes the insert before it runs, you will need to ignore the Id and pass it through to your function as well:

dbo.MyFunction(EmployeeId, ActivityId, Id) = 1

How would I write the function to know to check against those fields? I wrote a quick function that does the select and wants some params (EmployeeId and ActivityId) and then returns 1 or 0 if it exists, but how do I call that from my check constraint and pass it the values that are being inserted? — John, Dec 11 '14 at 23:32
The issue was actually caused by the insert happens before/during the check constraint, so i had to ignore the Id I was inserting from my select query (otherwise it would see it and fail!) — John, Dec 12 '14 at 17:57

Enforce Business Rules during SQL Server Insert (Check Constraint?)

1 Answers1