CREATE TABLE prace (ID_zam INT, od DATE, do DATE, ID_cin INT);
ALTER TABLE prace ADD CONSTRAINT prace_primary PRIMARY KEY(ID_zam,od);
ALTER TABLE prace ADD CONSTRAINT prace_alter_key UNIQUE(ID_zam,do);
ALTER TABLE prace ADD CONSTRAINT od_pred_do CHECK (od < do);

/*verze pro pripad, ze se nepripisuje zpetne*/
CREATE TRIGGER neprekryvani_prace_verze1 BEFORE INSERT ON prace
FOR EACH ROW
DECLARE chyba INTEGER;
BEGIN
  SELECT count(*) INTO chyba FROM prace prev
  WHERE prev.ID_zam=:new.ID_zam
  AND :new.od < prev.do;
  IF chyba>0 THEN
    raise_application_error(-20000,'jeste neskoncilo obdobi predchozi prace tohoto zamestnance');
  END IF;
END;

/*plna verze, pro obecny pripad, funguje jen pro single-insert*/
CREATE TRIGGER neprekryvani_prace_verze2 BEFORE INSERT ON prace
FOR EACH ROW
DECLARE chyba INTEGER;
BEGIN
  SELECT count(*) INTO chyba FROM prace prev
  WHERE prev.ID_zam=:new.ID_zam
  AND  (prev.od < :new.od AND :new.od < prev.do) OR 
       (prev.od < :new.do AND :new.do < prev.do) OR
       (:new.od <= prev.od AND prev.do <= :new.do); 
  IF chyba>0 THEN
    raise_application_error(-20001,'u tohoto zamestnance se toto obdobi prace prekryva s jeho jinou praci');
  END IF;
END;