-- Desenvolvimento de Gatilhos.

-- 12.	Um professor somente pode ter atribuições em disciplinas de cursos onde ele houver obtido as
--      habilitações indicativas para ministrar as disciplinas destes cursos.
--      Evento.: INSERT 
--      Tabela.: atribuicoes
--      Momento: antes de inserir (Before Insert - BI).
--      Determinando portanto o gatilho com nome: atribuicoesBI()
    
--      sabendo o código do professor (escrito na NOVA linha que está sendo inserida na tabela atribuicoes),
--      No comando INSERT INTO atribuicoes (idatribuicao, professorid, disciplinaid, qthorasatribuidas, dtatribuicao, dtcadatribuicao)
--                        VALUES (40,21,8,80,'2022-01-30','2022-10-19');
--      pesquisar as habilitações e cursos habilitados pelo professor. Sabendo os cursos nos quais o professor
--      tem habilitação, pesquisa-se quais as disciplinas destes cursos. Compara-se esta lista de disciplinas
--      com o código da disciplina da linha incluida... (SE a contagem de linha for > 1, SIGNIFICA que a disciplina
--      a ser inserida esta na lista dos cursos onde o professor tem habilitação).
--      iniciando o comando SQL que pesquisa em habilitacoes quais são as habilitações do
--      professor inserido na nova linha de atribuicoes.
--      Lembre que a nova linha inserida recebe a identificação NEW e tem todos os campos de atribuicoes
--      Portanto, o comando de veirificação se uma linha de atribuição pode ser inserida na tabela ATRIBUICOES pode ser escrito assim:
    SELECT count(*) FROM habilitacoes AS h INNER JOIN cursos AS c ON h.cursoid=c.idcurso
                                           INNER JOIN disciplinas AS d ON c.idcurso=d.cursoid
                    WHERE h.professorid='NEW.professorid' AND d.iddisciplina='NEW.disciplinaid'    
--      A edição da Função de Gatilho (no PG é indicada o nome: Trigger Function) pode ser feita com o código à seguir:
    CREATE OR REPLACE FUNCTION public."atribuicoesBI"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
    AS $BODY$
    declare
      vQtdDisc smallint;
    begin
      SELECT count(*) into vQtdDisc
             FROM habilitacoes AS h INNER JOIN cursos AS c ON h.cursoid=c.idcurso
                                    INNER JOIN disciplinas AS d ON c.idcurso=d.cursoid
             WHERE h.professorid='NEW.professorid' AND d.iddisciplina='NEW.disciplinaid';  
      if ( vQtdDisc>=1 )
      then
        return new;
      else
        raise exception 'A disciplina (%) não pode ser atribuida ao professor (%)',NEW.disciplinaid,NEW.professorid;
        return null;
      end if;
    end;
    
    $BODY$;
    ALTER FUNCTION public."atribuicoesBI"() OWNER TO postgres;

--      Esta função de gatilho deve ser 'vinculada' na tabela atribuicoes e o gatilho (na tabela) será disparado quando
--      o comando for executado, efetuando a consitencia ANTES de gravar o registro.
--      Acessando o compenente trigger na área de navegação sobre a tabela no PGAdmin 4 e escolhendo 'create'
--      Podemos escrever o comando em SQL que resulta na criação do gatilho, na forma:
    CREATE TRIGGER "BIatribuicoes"
    BEFORE INSERT
    ON public.atribuicoes
    FOR EACH ROW
    EXECUTE FUNCTION public."atribuicoesBI"();
--      Conclue-se, assim, a definição do Gatilho 12.
--      Abaixo segue-se uma forma alternativa de implementação do Gatilho 12.
--      Observações complementares podem ser observadas nas linhas abaixo.
--  ###################################################################################################################
--  ###################################################################################################################
--  ###################################################################################################################
--      Algoritmizando:
--      Determinando qual tabela deve ser tratada para registro: ATRIBUICOES.
--      Evento controlado: INSERT de linhas na tabela.
--      Momento: ANTES de executar o INSERT.
--      Procedimento:
--      Temos em NEW.professorid o código do professor ao qual se quer atribuir uma disciplina (em NEW.disciplinaid).
--      Partindo do professorid e de disciplinaid, o comando:
    select COUNT(*) into vQtdDisc from habilitacoes as h inner join cursos as c on c.idcurso=h.cursoid
                                    inner join disciplinas as d on c.idcurso=d.cursoid /* */
           where h.professorid='NEW.professorid' and d.iddisciplina='NEW.disciplinaid'
           group by h.professorid order by h.professorid;
--      conta quantas vezes o professor se habilitou em cursos que tem a disciplina que o professor quer ser atribuido a dar aulas.
--      SE o retorno for NULO significa que o professor NÃO PODE ser atribuido a dar aulas na disciplina.
--      Sendo assim
    IF ( vQtdDisc IS NULL )
    THEN
      RAISE EXCEPTION 'O Professor (%) NÃO TEM HABILITAÇÃO para ser Atribuido à Disciplina (%)', NEW.professorid, NEW.disciplinaid;
      RETURN NULL;
    ELSE
      RETURN NEW;
    END IF;
    
--      Deste modo pode-se escrever a função de gatilho assim:
    
-- FUNCTION: public.atribuicoesBI()

-- DROP FUNCTION IF EXISTS public."atribuicoesBI"();

CREATE OR REPLACE FUNCTION public."atribuicoesBI"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
   vQtdDisc smallint;
BEGIN
   select COUNT(*) into vQtdDisc from habilitacoes as h inner join cursos as c on c.idcurso=h.cursoid
                                    inner join disciplinas as d on c.idcurso=d.cursoid /* */
           where h.professorid='NEW.professorid' and d.iddisciplina='NEW.disciplinaid'
           group by h.professorid order by h.professorid;
    IF ( vQtdDisc IS NULL )
    THEN
      RAISE EXCEPTION 'O Professor (%) NÃO TEM HABILITAÇÃO para ser Atribuido à Disciplina (%)', NEW.professorid, NEW.disciplinaid;
      RETURN NULL;
    ELSE
      RETURN NEW;
    END IF;
END;
$BODY$;

ALTER FUNCTION public."atribuicoesBI"() OWNER TO postgres;