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?

That doesn't matter afaik. It's cargo cult and could be removed. — Alex Zhukovskiy, Sep 23 '15 at 09:50

score 1 · Answer 1 · answered Sep 23 '15 at 09:57

1

The result depends of data on table. It looks as you can't insert any records in the table with this constraint, if your table is empty. How many rows do you have on the table of Main? But in any cases better use

WHERE ReceptionBegin BETWEEN  DATEADD (minute , -30 , @receptionBegin) AND DATEADD (minute , 30 , @receptionBegin)

It will work faster, even, if you don't have a necessary index.

answered Sep 23 '15 at 09:57

Vyacheslav Shcherbak

94
5

Table is empty, because I cannot add a constraint to existing table if it's conflicting with data within. – Alex Zhukovskiy Sep 23 '15 at 11:59
So, you only need to use a correct syntax for CHECK CONSTRAINT. http://www.databasejournal.com/features/mssql/article.php/3811831/Using-Check-Constraints-to-Validate-Data-in-SQL-Server.htm – Vyacheslav Shcherbak Sep 23 '15 at 12:07

score 1 · Accepted Answer · answered Sep 23 '15 at 11:11

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

Great answer, thanks. I knew only triggers that works when add or update is called, but didn't know that I can _replace_ these calls. — Alex Zhukovskiy, Sep 23 '15 at 12:08

SQL check data period for insert

2 Answers2