
use dbMatInf;

delimiter ^


DROP TRIGGER IF EXISTS `trgCzasopisma_BI`^
CREATE TRIGGER trgCzasopisma_BI BEFORE INSERT ON tCzasopisma
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgDocuments_BI`^
CREATE TRIGGER trgDocuments_BI BEFORE INSERT ON tDocuments
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgFunkcje_BI`^
CREATE TRIGGER trgFunkcje_BI BEFORE INSERT ON tFunkcje
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgGranty_BI`^
CREATE TRIGGER trgGranty_BI BEFORE INSERT ON tGranty
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgInSeminaria_BI`^
CREATE TRIGGER trgInSeminaria_BI BEFORE INSERT ON tInSeminaria
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgInstytuty_BI`^
CREATE TRIGGER trgInstytuty_BI BEFORE INSERT ON tInstytuty
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgKomputery_BI`^
CREATE TRIGGER trgKomputery_BI BEFORE INSERT ON tKomputery
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgKonferencje_BI`^
CREATE TRIGGER trgKonferencje_BI BEFORE INSERT ON tKonferencje
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgKonferencjeUdzial_BI`^
CREATE TRIGGER trgKonferencjeUdzial_BI BEFORE INSERT ON tKonferencjeUdzial
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgKZOgloszenia_BI`^
CREATE TRIGGER trgKZOgloszenia_BI BEFORE INSERT ON tKZOgloszenia
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgOdnosniki_BI`^
CREATE TRIGGER trgOdnosniki_BI BEFORE INSERT ON tOdnosniki
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgOdnosnikiKategorie_BI`^
CREATE TRIGGER trgOdnosnikiKategorie_BI BEFORE INSERT ON tOdnosnikiKategorie
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgOgloszenia_BI`^
CREATE TRIGGER trgOgloszenia_BI BEFORE INSERT ON tOgloszenia
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgPZGrupy_AI`^
CREATE TRIGGER trgPZGrupy_AI AFTER INSERT ON tPZGrupy
  FOR EACH ROW 
  BEGIN
    update tPlanZajec set cKierunki = concat_ws(',', cKierunki, new.cKierunek) where cID = new.cZajeciaID; 
  END;
^


DROP TRIGGER IF EXISTS `trgPZGrupy_AU`^
CREATE TRIGGER trgPZGrupy_AU AFTER UPDATE ON tPZGrupy
  FOR EACH ROW 
  BEGIN
    if old.cKierunek <> new.cKierunek
    then
        if 0 = (select count(*) from tPZGrupy where cZajeciaID = old.cZajeciaID and cKierunek = old.cKierunek)
        then
            update tPlanZajec set cKierunki = replace(cKierunki, old.cKierunek, '') where cID = old.cZajeciaID; 
        end if;
        update tPlanZajec set cKierunki = concat_ws(',', cKierunki, new.cKierunek) where cID = new.cZajeciaID; 
   end if;
  END;
^


DROP TRIGGER IF EXISTS `trgPZGrupy_AD`^
CREATE TRIGGER trgPZGrupy_AD AFTER DELETE ON tPZGrupy
  FOR EACH ROW 
  BEGIN
    if 0 = (select count(*) from tPZGrupy where cZajeciaID = old.cZajeciaID and cKierunek = old.cKierunek)
    then
        update tPlanZajec set cKierunki = replace(cKierunki, old.cKierunek, '') where cID = old.cZajeciaID; 
    end if;
  END;
^


DROP TRIGGER IF EXISTS `trgPracownicy_BI`^
CREATE TRIGGER trgPracownicy_BI BEFORE INSERT ON tPracownicy
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


/*
DROP TRIGGER IF EXISTS `trgPracownicy_AU`^
CREATE TRIGGER trgPracownicy_AU after update on tPracownicy
  FOR EACH ROW
  BEGIN
    if 0 < locate('kza', new.cFunkcje) && (old.cFunkcje is null || locate('kza', old.cFunkcje) = 0)
        && (select cKierownik from tZaklady where cID = new.cZaklad) != new.cID
    then
        update tZaklady set cKierownik = new.cID where cID = new.cZaklad;
    end if;
    if (new.cFunkcje is null || locate('kza', new.cFunkcje) = 0) && 0 < locate('kza', old.cFunkcje)
        && (select cKierownik from tZaklady where cID = new.cZaklad) = new.cID
    then
        update tZaklady set cKierownik = NULL where cID = new.cZaklad;
    end if;
    if 0 < locate('sek', new.cFunkcje) && (old.cFunkcje is null || locate('sek', old.cFunkcje) = 0)
        && (select cSekretarz from tZaklady where cID = new.cZaklad) != new.cID
    then
        update tZaklady set cSekretarz = new.cID where cID = new.cZaklad;
    end if;
    if (new.cFunkcje is null || locate('sek', new.cFunkcje) = 0) && 0 < locate('sek', old.cFunkcje)
        && (select cSekretarz from tZaklady where cID = new.cZaklad) = new.cID
    then
        update tZaklady set cSekretarz = NULL where cID = new.cZaklad;
    end if;
  END;
*/

DROP TRIGGER IF EXISTS `trgPublikacje_BI`^
CREATE TRIGGER trgPublikacje_BI BEFORE INSERT ON tPublikacje
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgPublikacje_BU`^
CREATE TRIGGER trgPublikacje_BU BEFORE UPDATE ON tPublikacje
  FOR EACH ROW
  BEGIN
    if new.cStatus <> old.cStatus then set
        new.cStatusTS = now();
    end if;
    case new.cRodzaj
        when 'article' then set
            new.cWydawnictwoZwarteID = null,
            new.cMonth = null,
            new.cType = null,
            new.cChapter = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cEdition = null,
            new.cSeries = null,
            new.cEditor = null,
            new.cPublisher = null,
            new.cAddress = null,
            new.cInstitution = null,
            new.cOrganization = null,
            new.cHowpublished = null,
            new.cISSN = null,
            new.cEISSN = null,
            new.cISBN = null,
            new.cEISBN = null;
        when 'book' then set
            new.cWydawnictwoZwarteID = null,
            new.cJournal = null,
            new.cNumber = null,
            new.cPages = null,
            new.cType = null,
            new.cChapter = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cEditor = null,
            new.cInstitution = null,
            new.cOrganization = null,
            new.cHowpublished = null,
            new.cISSN = null,
            new.cEISSN = null;
        when 'booklet' then set
            new.cCzasopismoID = null,
            new.cWydawnictwoZwarteID = null,
            new.cJournal = null,
            new.cVolume = null,
            new.cNumber = null,
            new.cPages = null,
            new.cType = null,
            new.cChapter = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cEdition = null,
            new.cSeries = null,
            new.cEditor = null,
            new.cPublisher = null,
            new.cInstitution = null,
            new.cOrganization = null,
            new.cISSN = null,
            new.cEISSN = null,
            new.cISBN = null,
            new.cEISBN = null;
        when 'inbook' then set
            new.cCzasopismoID = null,
            new.cWydawnictwoZwarteID = null,
            new.cJournal = null,
            new.cNumber = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cEditor = null,
            new.cInstitution = null,
            new.cOrganization = null,
            new.cHowpublished = null,
            new.cISSN = null,
            new.cEISSN = null;
        when 'incollection' then set
            new.cCzasopismoID = null,
            new.cJournal = null,
            new.cNumber = null,
            new.cType = null,
            new.cChapter = null,
            new.cInstitution = null,
            new.cOrganization = null,
            new.cHowpublished = null;
        when 'inproceedings' then set
            new.cCzasopismoID = null,
            new.cJournal = null,
            new.cNumber = null,
            new.cType = null,
            new.cChapter = null,
            new.cEdition = null,
            new.cInstitution = null,
            new.cHowpublished = null;
        when 'manual' then set
            new.cCzasopismoID = null,
            new.cWydawnictwoZwarteID = null,
            new.cJournal = null,
            new.cVolume = null,
            new.cNumber = null,
            new.cPages = null,
            new.cType = null,
            new.cChapter = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cSeries = null,
            new.cEditor = null,
            new.cPublisher = null,
            new.cInstitution = null,
            new.cHowpublished = null,
            new.cISSN = null,
            new.cEISSN = null,
            new.cISBN = null,
            new.cEISBN = null;
        when 'techreport' then set
            new.cCzasopismoID = null,
            new.cWydawnictwoZwarteID = null,
            new.cJournal = null,
            new.cVolume = null,
            new.cNumber = null,
            new.cPages = null,
            new.cChapter = null,
            new.cBooktitle = null,
            new.cBooksubtitle = null,
            new.cConferencedate = null,
            new.cEdition = null,
            new.cSeries = null,
            new.cEditor = null,
            new.cPublisher = null,
            new.cOrganization = null,
            new.cHowpublished = null,
            new.cISSN = null,
            new.cEISSN = null,
            new.cISBN = null,
            new.cEISBN = null;
    end case;
    if 0 < new.cCzasopismoID and new.cRodzaj = 'article' then set
        new.cWydawnictwoZwarteID = null,
        new.cJournal = null;
    end if;
    if 0 < new.cWydawnictwoZwarteID and (new.cRodzaj = 'incollection' or new.cRodzaj = 'inproceedings') then set
        new.cCzasopismoID = null,
        new.cBooktitle = null,
        new.cBooksubtitle = null,
        new.cConferencedate = null,
        new.cSeries = null,
        new.cVolume = null,
        new.cMonth = null,
        new.cYear = null,
        new.cEdition = null,
        new.cEditor = null,
        new.cISSN = null,
        new.cEISSN = null,
        new.cISBN = null,
        new.cEISBN = null,
        new.cOrganization = null,
        new.cPublisher = null,
        new.cAddress = null;
    end if;
  END;
^

DROP TRIGGER IF EXISTS `trgSylabusy_BI`^
CREATE TRIGGER trgSylabusy_BI BEFORE INSERT ON tSylabusy
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgWydarzenia_BI`^
CREATE TRIGGER trgWydarzenia_BI BEFORE INSERT ON tWydarzenia
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgWydawnictwaZwarte_BI`^
CREATE TRIGGER trgWydawnictwaZwarte_BI BEFORE INSERT ON tWydawnictwaZwarte
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgWydawnictwaZwarteEdytorzy_AI`^
CREATE TRIGGER trgWydawnictwaZwarteEdytorzy_AI after insert on tWydawnictwaZwarteEdytorzy
  FOR EACH ROW 
  BEGIN
    update tWydawnictwaZwarte set cEdytor = (select 
        group_concat(concat_ws(' ', cImie, cNazwisko) order by cKolejnosc, cNazwisko, cImie separator ', ') 
            from tWydawnictwaZwarteEdytorzy where cWydawnictwoZwarteID = new.cWydawnictwoZwarteID) 
        where cID = new.cWydawnictwoZwarteID;
  END;
^

DROP TRIGGER IF EXISTS `trgWydawnictwaZwarteEdytorzy_AU`^
CREATE TRIGGER trgWydawnictwaZwarteEdytorzy_AU after update on tWydawnictwaZwarteEdytorzy
  FOR EACH ROW 
  BEGIN
    update tWydawnictwaZwarte set cEdytor = (select 
        group_concat(concat_ws(' ', cImie, cNazwisko) order by cKolejnosc, cNazwisko, cImie separator ', ') 
            from tWydawnictwaZwarteEdytorzy where cWydawnictwoZwarteID = new.cWydawnictwoZwarteID) 
        where cID = new.cWydawnictwoZwarteID;
  END;
^
  
DROP TRIGGER IF EXISTS `trgWydawnictwaZwarteEdytorzy_AD`^
CREATE TRIGGER trgWydawnictwaZwarteEdytorzy_AD after delete on tWydawnictwaZwarteEdytorzy
  FOR EACH ROW 
  BEGIN
    update tWydawnictwaZwarte set cEdytor = (select 
        group_concat(concat_ws(' ', cImie, cNazwisko) order by cKolejnosc, cNazwisko, cImie separator ', ') 
            from tWydawnictwaZwarteEdytorzy where cWydawnictwoZwarteID = old.cWydawnictwoZwarteID) 
        where cID = old.cWydawnictwoZwarteID;
  END;
^


DROP TRIGGER IF EXISTS `trgZaklady_BI`^
CREATE TRIGGER trgZaklady_BI BEFORE INSERT ON tZaklady
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^

DROP TRIGGER IF EXISTS `trgZaklady_AU`^
CREATE TRIGGER trgZaklady_AU after update on tZaklady
  FOR EACH ROW
  BEGIN
    if new.cKierownik != old.cKierownik || new.cKierownik is null || old.cKierownik is null then
        update tPracownicy set cFunkcje = concat_ws(',', cFunkcje, 'kza') where cID = new.cKierownik;
        update tPracownicy set cFunkcje = replace(cFunkcje, 'kza', '') where cID = old.cKierownik;
    end if;
    if new.cSekretarz != old.cSekretarz || new.cSekretarz is null || old.cSekretarz is null then
        update tPracownicy set cFunkcje = concat_ws(',', cFunkcje, 'sek') where cID = new.cSekretarz;
        update tPracownicy set cFunkcje = replace(cFunkcje, 'sek', '') where cID = old.cSekretarz;
    end if;
  END;


DROP TRIGGER IF EXISTS `trgZaSeminaria_BI`^
CREATE TRIGGER trgZaSeminaria_BI BEFORE INSERT ON tZaSeminaria
  FOR EACH ROW
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^


DROP TRIGGER IF EXISTS `trgZespoly_BI`^
CREATE TRIGGER trgZespoly_BI BEFORE INSERT ON tZespoly
  FOR EACH ROW 
  BEGIN
    SET new.cCT = CURRENT_TIMESTAMP;
  END;
^

delimiter ;
