create exception a 'Неверный ввод данных';

set term!!;

/* триггер на добавление */
CREATE TRIGGER ins_tr for v_chel_spisok
BEFORE INSERT
AS
DECLARE VARIABLE f_ID smallint;
DECLARE VARIABLE i_ID smallint;
DECLARE VARIABLE o_ID smallint;
BEGIN

IF(EXISTS(SELECT * from fam_spr f_s where f_s.familia=new.familia)
AND EXISTS(SELECT * from im_spr i_s where i_s.imia=new.imia)
AND EXISTS(SELECT * from otch_spr o_s where o_s.otchestvo=new.otchestvo)
AND EXISTS(SELECT * from adress ad where ad.ID_adr=new.id_adres1))
THEN
BEGIN

SELECT ID_fam from fam_spr f_s where f_s.familia=NEW.familia into :f_ID;
SELECT ID_im from  im_spr i_s where i_s.imia=NEW.imia into :i_ID;
SELECT ID_otch from otch_spr o_s where o_s.otchestvo=NEW.otchestvo into :o_ID;

INSERT INTO people(ID, familia, imia, otchestvo, birthday, id_adres1) values(null, :ID_fam, :ID_im, :ID_otch, new.birthday, new.id_adres1);
END
ELSE
exception a;
END!!


/* триггер на удаление */
CREATE TRIGGER del_tr for v_chel_spisok
BEFORE DELETE
AS
DECLARE VARIABLE fa1 smallint;
DECLARE VARIABLE im1 smallint;
DECLARE VARIABLE ot1 smallint;
BEGIN

SELECT ID_fam from fam_spr f_s where f_s.familia=OLD.familia into :fa1;
SELECT ID_im from  im_spr i_s where i_s.imia=OLD.imia into :im1;
SELECT ID_otch from otch_spr o_s where o_s.otchestvo=OLD.otchestvo into :ot1;

DELETE FROM people p where p.familia=:fa1
AND p.imia=:im1
AND p.otchestvo=:ot1
AND p.birthday=OLD.birthday
AND p.id_adres1=OLD.id_adres1;
END!!


/* триггер на изменение */
CREATE TRIGGER upd_tr for v_chel_spisok
BEFORE UPDATE
AS
DECLARE VARIABLE f_ID smallint;
DECLARE VARIABLE i_ID smallint;
DECLARE VARIABLE o_ID smallint;
DECLARE VARIABLE OLD_f_ID smallint;
DECLARE VARIABLE OLD_i_ID smallint;
DECLARE VARIABLE OLD_o_ID smallint;
BEGIN
IF(EXISTS(SELECT * from fam_spr f_s where f_s.familia=NEW.familia)
AND EXISTS(SELECT * from im_spr i_s where i_s.imia=NEW.imia)
AND EXISTS(SELECT * from otch_spr o_s where o_s.otchestvo=NEW.otchestvo)
AND EXISTS(SELECT * from adress ad where ad.ID_adr=NEW.id_adres1)) 
THEN
BEGIN

SELECT ID_fam from fam_spr f_s where f_s.familia=NEW.familia into :f_ID;
SELECT ID_im from  im_spr i_s where i_s.imia=NEW.imia into :i_ID;
SELECT ID_otch from otch_spr o_s where o_s.otchestvo=NEW.otchestvo into :o_ID;
SELECT ID_fam from fam_spr f_s where f_s.familia=OLD.familia into :OLD_f_ID;
SELECT ID_im from  im_spr i_s where i_s.imia=OLD.imia into :OLD_i_ID;
SELECT ID_otch from otch_spr o_s where o_s.otchestvo=OLD.otchestvo into :OLD_o_ID;
UPDATE people p set p.familia=:f_ID, p.imia=:i_ID, p.otchestvo=:o_ID, p.birthday=OLD.birthday 
AND p.id_adres1=old.id_adres1;
END
ELSE
exception a;
END!!

set term;!!