create or replace trigger "DOGOVOR_S_KANDIDATOM_SAME_PROF"
for
insert or update on "DOGOVOR_S_KANDIDATOM"
compound trigger
    type dg_rec_type is record (data DOGOVOR_S_KANDIDATOM.DATA_DOGOVORA%type, idProf DOGOVOR_S_KANDIDATOM.PROFESSII_IDPROFESSII%type);
    type dg_tab_type is table of dg_rec_type index by varchar2(30);
    dg_tab dg_tab_type;
    n pls_integer;
BEFORE STATEMENT IS
    begin
    dg_tab.delete;
    for rec in (select IDDOGOVOR_S_KANDIDATOM, DATA_DOGOVORA, PROFESSII_IDPROFESSII FROM DOGOVOR_S_KANDIDATOM) LOOP
        dg_tab(rec.IDDOGOVOR_S_KANDIDATOM).data := rec.DATA_DOGOVORA;
        dg_tab(rec.IDDOGOVOR_S_KANDIDATOM).idProf := rec.PROFESSII_IDPROFESSII;
        null;
    end loop;
end BEFORE STATEMENT;
AFTER EACH ROW IS
    BEGIN
        DBMS_OUTPUT.ENABLE;
        select count(*)
        into n
        from (Select dg_tab.data
            from dg_tab
            Where :NEW.PROFESSII_IDPROFESSII = dg_tab.idProf
            group by dg_tab.data
            having TO_CHAR(:NEW.DATA_DOGOVORA, 'MM') = TO_CHAR(dg_tab.data, 'MM'));
        htp.p('Количество претендентов на ту же должность'||n);
        NULL;
    END AFTER EACH ROW;
end "DOGOVOR_S_KANDIDATOM_SAME_PROF";

create or replace trigger "DOGOVOR_S_KANDIDATOM_SAME_PROF"
for
insert or update on "DOGOVOR_S_KANDIDATOM"
compound trigger
V_LIST DG_TBL_TYPE := DG_TBL_TYPE();
BEFORE EACH ROW IS
    BEGIN
        DBMS_OUTPUT.ENABLE;
        V_LIST.COUNT;
        V_LIST(V_LIST.COUNT).DATA := :NEW.DATA_DOGOVORA;
        V_LIST(V_LIST.COUNT).IdProf := :NEW.PROFESSII_IDPROFESSII;
        NULL;
    END BEFORE EACH ROW;
AFTER STATEMENT IS
    begin
    FOR V_REC IN (
              SELECT  D.PROFESSII_IDPROFESSII,
                      TRUNC(D.DATA_DOGOVORA,'MM') DATA,
                      COUNT(*) CNT
                FROM  DOGOVOR_S_KANDIDATOM D,
                      TABLE(V_LIST) T
                WHERE D.PROFESSII_IDPROFESSII = T.idProf
                  AND TRUNC(D.DATA_DOGOVORA,'MM') = TRUNC(T.data,'MM')
                GROUP BY D.PROFESSII_IDPROFESSII,
                         TRUNC(D.DATA_DOGOVORA,'MM')
             ) LOOP
      HTP.P('Количество претендентов на должность '|| V_REC.PROFESSII_IDPROFESSII || ' за ' || TO_CHAR(V_REC.DATA,'FMMonth YYYY: ') || V_REC.CNT);
    END LOOP;
end AFTER STATEMENT;
end "DOGOVOR_S_KANDIDATOM_SAME_PROF";

create or replace trigger "DOGOVOR_S_KANDIDATOM_SAME_PROF"
for
insert or update on "DOGOVOR_S_KANDIDATOM"
compound trigger
V_LIST DG_TBL_TYPE := DG_TBL_TYPE();
BEFORE EACH ROW IS
    BEGIN
        V_LIST.EXTEND;
        V_LIST(V_LIST.COUNT).DATA := :NEW.DATA_DOGOVORA;
        V_LIST(V_LIST.COUNT).IdProf := :NEW.PROFESSII_IDPROFESSII;
        NULL;
    END BEFORE EACH ROW;
AFTER STATEMENT IS
    begin
    FOR V_REC IN (
              SELECT  D.PROFESSII_IDPROFESSII,
                      TRUNC(D.DATA_DOGOVORA,'MM') DATA,
                      COUNT(*) CNT
                FROM  DOGOVOR_S_KANDIDATOM D,
                      TABLE(V_LIST) T
                WHERE D.PROFESSII_IDPROFESSII = T.idProf
                  AND TRUNC(D.DATA_DOGOVORA,'MM') = TRUNC(T.data,'MM')
                GROUP BY D.PROFESSII_IDPROFESSII,
                         TRUNC(D.DATA_DOGOVORA,'MM')
             ) LOOP
      HTP.P('Количество претендентов на должность '|| V_REC.PROFESSII_IDPROFESSII || ' за ' || TO_CHAR(V_REC.DATA,'FMMonth YYYY: ') || V_REC.CNT);
    END LOOP;
end AFTER STATEMENT;
end "DOGOVOR_S_KANDIDATOM_SAME_PROF";