CREATE OR REPLACE FUNCTION trig_refintegr_zchu_P() RETURNS OPAQUE AS '
DECLARE ktab CHAR(1);
BEGIN
  IF TG_OP = ''UPDATE'' THEN
IF NEW.id <> OLD.id THEN
  RAISE EXCEPTION ''referential integrity violation - You can not update PRIMARY KEY column on table %'', 
TG_RELNAME;
END IF;
RETURN NEW;
  END IF;

  ktab := CASE TG_RELNAME 
WHEN ''mchu'' THEN ''m'' 
WHEN ''vchu'' THEN ''v''
WHEN ''ps''   THEN ''s'' 
  END;  

  IF EXISTS(SELECT id FROM ZCHU_RezervacniKnihy WHERE idoo = OLD.id
AND tanoo = ktab) THEN
RAISE EXCEPTION ''referential integrity violation - key in % still referenced from ZCHU_rezervacniKnihy'', 
  TG_RELNAME;
  END IF;
  RETURN OLD;
END;
' LANGUAGE 'plpgsql'

CREATE TRIGGER refintegr_zchu_p_mchu
  BEFORE DELETE OR UPDATE ON MCHU
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P();

CREATE TRIGGER refintegr_zchu_p_vchu
  BEFORE DELETE OR UPDATE ON VCHU
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P();

CREATE TRIGGER refintegr_zchu_p_ps
  BEFORE DELETE OR UPDATE ON PS
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_P();

PL/pgSQL funkce trig_refintegr_zchu_F() nedovol� p�i�adit do tabulky
rezerva�n�ch knih odkaz na neexistuj�c� z�konem chr�n�n� �zem�.

CREATE OR REPLACE FUNCTION trig_refintegr_zchu_F() RETURNS OPAQUE AS '
DECLARE r RECORD; tab VARCHAR(20);
BEGIN
  IF TG_OP = ''UPDATE'' THEN
IF NEW.id <> OLD.id THEN
  RAISE EXCEPTION ''referential integrity violation - You can not update PRIMARY KEY column on table %'', TG_RELNAME;
END IF;
  END IF;
  IF NEW.taboo IS NULL THEN
RETURN NEW;  -- nelze pokra�ovat
  END IF;
  IF NOT NEW.taboo IN (''m'',''v'',''s'') THEN
RETURN NEW;
  END IF;
  SELECT INTO r CASE NEW.taboo
WHEN ''m'' THEN (SELECT id FROM MCHU WHERE id=NEW.idoo)
WHEN ''v'' THEN (SELECT id FROM VCHU WHERE id=NEW.idoo)
WHEN ''s'' THEN (SELECT id FROM PS WHERE id=NEW.idoo)
  END AS id;
  IF r.id IS NULL THEN
tab := CASE NEW.taboo
  WHEN ''m'' THEN ''MCHU''
  WHEN ''v'' THEN ''VCHU''
  WHEN ''s'' THEN ''PS''
END;   
RAISE EXCEPTION ''referential integrity violation - key referenced from % not found in %'', TG_RELNAME, tab;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER refintegr_zchu_f_ZCHU_RK
  BEFORE INSERT OR UPDATE ON ZCHU_rezervacniKnihy
  FOR EACH ROW EXECUTE PROCEDURE trig_refintegr_zchu_f();