Using sql function on CHECK constraint for newly inserted row

Question

First of all i need help with this for my bachelor thesis. I'm doing the whole database on sql server 2008 Release 2. The problem is with check constraint that is using a function that is working on her own but not with the use in the constraint. The result of the constraint should be something like this: An employee could go only on one bussines trip per day.

Table Bussines trips:

CREATE TABLE SluzebniCesta(
idSluzCesty int PRIMARY KEY NOT NULL,
DatumCesty DATE NOT NULL,
CasOdjezdu TIME(0) NOT NULL,
CasPrijezdu TIME(0),
CONSTRAINT Odjezd_prijezd CHECK(CasPrijezdu > DATEADD(hour,2,CasOdjezdu))
);

Table that contains the employs that goes on bussines trip:

CREATE TABLE ZamNaCeste(
idZamNaCeste int PRIMARY KEY NOT NULL,
SluzebCestaID int NOT NULL,
ZamestnanecID int NOT NULL,
FOREIGN KEY (ZamestnanecID) REFERENCES Zamestnanec(idZamestnance),
FOREIGN KEY (SluzebCestaID) REFERENCES SluzebniCesta(idSluzCesty)
);

Foreign key ZamestnanecID is an employee's id and SluzebCestaID is the bussines trip id.

Now the function :

CREATE FUNCTION myCheckZamNaCeste(@SluzebCestaID int, @ZamestnanecID int)
RETURNS int
AS
BEGIN
    DECLARE @retVal int;
    DECLARE @Zamestnanec int;
    DECLARE @SluzebniCesta int;
    SET @Zamestnanec = (SELECT idZamestnance FROM Zamestnanec WHERE idZamestnance=@ZamestnanecID);
    SET @SluzebniCesta = (SELECT idSluzCesty FROM SluzebniCesta WHERE idSluzCesty=@SluzebCestaID);
    IF EXISTS ( SELECT DatumCesty FROM SluzebniCesta
    WHERE idSluzCesty = @SluzebniCesta
    AND DatumCesty IN (SELECT DatumCesty FROM ZamNaCeste
    LEFT JOIN SluzebniCesta
    ON ZamNaCeste.SluzebCestaID = SluzebniCesta.idSluzCesty
    WHERE ZamestnanecID=@Zamestnanec))
    BEGIN
    SET @retVal=0;
    END
    ELSE
    BEGIN
    SET @retVal=1;
    END
    return @retVal
END
GO

And the alter table for the table that contains evidence of employee and their bussines trips:

ALTER TABLE ZamNaCeste
ADD CONSTRAINT check_cesty_zamestnance CHECK(dbo.myCheckZamNaCeste(SluzebCestaID,ZamestnanecID)=1);

And when I try to enter any new row the constraint is broken even if the function gives the right data. return 1 is the good result ....

Answer 1

In the first place, I'm not sure but it looks like the two set statements in the function are going out to retrieve from tables exactly the same values they already have from being passed in as parameters.

In the second place, I don't see anything limiting trips in the same day. Anywhere.

If you wanted to limit a trip by an employee to one per day, that is easy.

CREATE TABLE ZamNaCeste(
    idZamNaCeste int PRIMARY KEY NOT NULL,
    SluzebCestaID int NOT NULL,
    ZamestnanecID int NOT NULL,
    TripDate      date not null,
    FOREIGN KEY (ZamestnanecID) REFERENCES Zamestnanec(idZamestnance),
    FOREIGN KEY (SluzebCestaID) REFERENCES SluzebniCesta(idSluzCesty),
    constraint UQ_OneTripPerDay unique( ZamestnanecID, TripDate )
);

The unique constraint ensures the same employee cannot log more than one trip on the same day.