SQL check data period for insert

Question

I have a table with datetime and want to check if there is some entry with datetime in +-30 minutes period relative to inserted value. So i write this constraint:

USE [Test]
GO
/****** Object:  UserDefinedFunction [dbo].[CanInsertReception]    Script Date: 23.09.2015 12:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CanInsertReception] (@receptionBegin datetime)
RETURNS bit
AS 
BEGIN
  DECLARE @result bit;
  IF EXISTS(SELECT * FROM Main
            where DATEDIFF(MINUTE, ReceptionBegin, @receptionBegin) <= 30 or DATEDIFF(MINUTE, @receptionBegin, ReceptionBegin) <= 30)
    SET @result = 0
  ELSE
    SET @result = 1

  return @result;    
END;
GO;

ALTER TABLE Main 
  ADD CONSTRAINT CheckIfCanInsertReception 
  CHECK (dbo.CanInsertReception(ReceptionBegin) = 1); 

but when I try to insert any data this check do not allow to insert it, But when I execute this script, which is doing the same:

  DECLARE @receptionBegin datetime = '2015-01-01 09:00:00'

  DECLARE @result bit;
  IF EXISTS(SELECT * FROM Main
            where DATEDIFF(MINUTE, ReceptionBegin, @receptionBegin) <= 30 or DATEDIFF(MINUTE, @receptionBegin, ReceptionBegin) <= 30)
    SET @result = 0
  ELSE
    SET @result = 1

  SELECT @result

I get expected output 1

What am I doing wrong here?

Answer 1

Using a trigger instead of a check constraint may be a good option!

If created a test table like this:

ReceptionId ReceptionText   ReceptionBegin      ReceptionEnd 
1               A           2015-09-23 13:00    2015-09-23 13:45 
2               B           2015-09-23 14:00    2015-09-23 14:45
3               C           2015-09-23 15:00    2015-09-23 15:45
4               D           2015-09-23 16:00    2015-09-23 16:45

Trigger looks like this:

    CREATE TRIGGER dbo.IO_IU_Test ON dbo.Test
    INSTEAD OF INSERT,UPDATE
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @IsUpdate BIT = 0

        DECLARE @ErrMessage NVARCHAR(255)

        IF EXISTS (SELECT TOP 1 1 FROM deleted d)
            SET @IsUpdate = 1

        SET @ErrMessage = 'Value(s) can not be ' + IIF(@IsUpdate=1,'updated','inserted') + ' - there are existing Receptions which are less than 30 Minutes away!'

        IF EXISTS(
            SELECT TOP 1 1
            FROM    inserted i
                    INNER JOIN dbo.Test t ON
                      ABS(DATEDIFF(MINUTE,i.ReceptionBegin,t.ReceptionBegin)) <= 30
            )

        BEGIN
            RAISERROR(@ErrMessage,16,1)
            RETURN
        END

        IF @IsUpdate = 1
        BEGIN
            UPDATE  t
            SET     t.ReceptionText = i.ReceptionText,
                    t.ReceptionBegin = i.ReceptionBegin,
                    t.ReceptionEnd = t.ReceptionEnd
            FROM    dbo.Test t
                    INNER JOIN inserted i ON t.ReceptionId = i.ReceptionId
        END
        ELSE
        BEGIN
            INSERT INTO dbo.Test(ReceptionText,ReceptionBegin,ReceptionEnd)
            SELECT i.ReceptionText, i.ReceptionEnd, i.ReceptionEnd FROM inserted i
        END
    END

Advantages:

• Set based operation
• You can raise custom error message
• can be improved by using try-catch and/or transactions

Test:

INSERT INTO dbo.Test (ReceptionText, ReceptionBegin, ReceptionEnd)
VALUES ('E','2015-09-23 12:31','2015-09-23 12:36')

you get an error: Value(s) can not be inserted - there are existing Receptions which are less than 30 Minutes away!

UPDATE dbo.Test SET ReceptionBegin = '2015-09-23 13:30'
WHERE ReceptionId = 1

you get an error: Value(s) can not be updated - there are existing Receptions which are less than 30 Minutes away!

INSERT INTO dbo.Test (ReceptionText, ReceptionBegin, ReceptionEnd)
VALUES ('E','2015-09-23 10:00','2015-09-23 10:21')

this is working -> no receptions begin between 09:30 and 10:30