--- In firebird-support@yahoogroups.com, Nikolaus Kern wrote:

> Hello all,
>
> Using now Firebird for some time I am facing now a question where I > am simply stuck.
>
> *) There is a simple table to store the allocation of employees to
> specific projects
> *) Its stored with a start- and enddate
> *) The database should check now if the same employee is booked to > the same or an overlapping timeframe
>
> The code below works perfect until a BAUSTELLE (= project) is
> renamed. In this case the CHECK condition fires and seems to find
> itself.
>
> Is there something like a rowID I could use in this context?
>
> Thanks a lot in advance
>
> Niko
>
> ***********************************************
> CREATE TABLE DETAILPLANUNG (
> BAUSTELLE CHAR(30) CHARACTER SET NONE NOT NULL COLLATE NONE,
> MITARBEITER INTEGER,
> STARTDATUM DATE,
> STOPDATUM DATE);
>
>
> ALTER TABLE DETAILPLANUNG ADD CONSTRAINT CHK_DETAILPLANUNG
> CHECK (EXISTS
> (SELECT
> *
> FROM DETAILPLANUNG
> WHERE MITARBEITER = NEW.MITARBEITER AND
> ((NEW.STARTDATUM BETWEEN STARTDATUM AND STOPDATUM) OR
> (NEW.STOPDATUM BETWEEN STARTDATUM AND STOPDATUM)) ));

I don't know whether RDB$DB_KEY (that's the closest Firebird has to rowid) can be used for this or not (feel free to try), but generally speaking you could easily avoid this by adding an ID field that you simply populated with a BEFORE INSERT trigger and a generator - i.e.

CREATE TABLE DETAILPLANUNG (
ID INTEGER PRIMARY KEY,
BAUSTELLE CHAR(30) CHARACTER SET ISO8859_1 NOT NULL COLLATE DE_DE,
MITARBEITER INTEGER,
STARTDATUM DATE,
STOPDATUM DATE);

CREATE GENERATOR ID_GEN;

SET TERM ^^ ;
CREATE TRIGGER DETAILPLANUNG_ID FOR DETAILPLANUNG ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
if ( (new.ID is null) or (new.ID = 0) )
then new.ID = gen_id(ID_GEN, 1);
END ^^
SET TERM ; ^^

ALTER TABLE DETAILPLANUNG ADD CONSTRAINT CHK_DETAILPLANUNG
CHECK (NOT EXISTS
(SELECT
*
FROM DETAILPLANUNG DP
WHERE DP.MITARBEITER = NEW.MITARBEITER AND
((NEW.STARTDATUM BETWEEN DP.STARTDATUM AND DP.STOPDATUM) OR
(NEW.STOPDATUM BETWEEN DP.STARTDATUM AND DP.STOPDATUM)) AND
(NEW.ID <> DP.ID) ));

HTH,
Set