-- ########################################################################################################################################
-- Esta série de arquivos demonstra o desenvolvimento de gatilhos.
-- Desde a fase de percepção do problema, passando pelo desenolvimento do algoritmo e implementação em PL/PGSQL do PostgreSQL.
-- 
-- ########################################################################################################################################
--
-- A TF é escrita em pl/pgsql
   -- A TF não tem rótulos nas linhas (as linhas NÃO são Identificadas NEM numeradas)
   -- Todos os comandos devem ter uma Declaração de Inicio e Fim.
-- A TF tem uma estrutura básica:
-- Declaração de Variáveis
   -- As Variáveis devem ser Declaradas com Nome E Tipo de Dado
      -- Os Tipos de Dados de Variáveis são os mesmos dos tipos de dados de Campos de Tabelas
-- Bloco de Execução
   -- Inicia SEMPRE com a palavra BEGIN e termina com END;
   -- Os Comandos de Desvio Condicional são estruturados
      -- Apresentam Inicio E Fim 
== Comandos SQL ou de Desvio condicional implementando o ALGORTIMO da TF
-- Final do Bloco de Execução
-- ########################################################################################################################################
--
-- Exemplos de gatilho
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 01 || Criação: 2026-1 | Revisão: 2026-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Na tabela Clientes, o código do cliente (cpcliente) deve ser incrementado em 5 unidades a cada nova linha inserida na tabela.
-- Desenvolva o Gatilho na tabela e função de gatilho que execute este controle.

-- Determinando o Nome:
-- Tabela: clientes
-- Momento: ANTES
-- Evento: Insert
-- Antes de inserir verificar qual o último valor de cpcliente incrementar CINCO unidades ao valor e
-- retornar o valor para a posição do cpcliente no comando insert.
-- Este problema pode ser resolvido com a seguinte Trigger Function()
-- O Código a seguir pode ser copiado da ABA SQL quando se acessa o componente TRIGGER FUNCTION e marcando 
-- FUNCTION: public.clientesBI()
-- DROP FUNCTION IF EXISTS public."clientesBI"();
CREATE OR REPLACE FUNCTION public."clientesBI"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
vmax smallint;

begin
 select max(cpcliente)+5 INTO vmax from clientes;
 IF ( vmax IS NULL )
 THEN
   NEW.cpcliente=5;
 ELSE
   NEW.cpcliente=vmax;
 END IF;
 return NEW;
end;
$BODY$;

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

COMMENT ON FUNCTION public."clientesBI"()
    IS 'Determinando o valor da cpcliente (chave primária) incrementado de 5 em 5 unidades.';

-- Agora vamos definir o Gatilho NA Tabela.
-- O código abaixo pode ser copiado da ABA SQL 
-- Trigger: BIclientes

-- DROP TRIGGER IF EXISTS "BIclientes" ON public.clientes;

CREATE OR REPLACE TRIGGER "BIclientes"
    BEFORE INSERT
    ON public.clientes
    FOR EACH ROW
    EXECUTE FUNCTION public."clientesBI"();

COMMENT ON TRIGGER "BIclientes" ON public.clientes
    IS 'Gatilho que vai ''disparar'' a função de gatilho.';

-- Aqui apresenta-se as telas necessárias para a DOCUMENTAÇÃO a ser entregue como trabalho
-- Texto das definições das Funções de Gatilho e Gatilho 01
-- Função de Gatilho: clienteBI
--
-- Método de desenvolvimento deste 'relatório'.
-- -- Acesse o componente Trigger Functions e MARQUE a Trigger Function que foi escrita.
-- -- No paonel da diretira do pgAdmin 4 acesse a ABA SQL.
-- -- Copie e Cole o conteúdo da Aba SQL em um arquivo texto (abra o NotePad++ e crie um novo
--    documento)
-- -- O Arquivo pode ficar como a sequência de comandos a seguir.

-- FUNCTION: public.clientesBI()
-- DROP FUNCTION IF EXISTS public."clientesBI"();
CREATE OR REPLACE FUNCTION public."clientesBI"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
  vMAX smallint;
begin
  select max(cpcliente) INTO vMAX from clientes;
  if vMAX IS NULL
  then
     vMAX:=5;
  else
     vMAX:=vMAX+5;
  end if;
  NEW.cpcliente:=vMAX;
  return new;
end;
$BODY$;

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

-- DEPOIS De Copiado o conteúdo de definição da Trigger Function, no lado direito acesse
-- o componente TABLES.
-- MARQUE a Tabela que contém o Gatilho e Clique EXATAMENTE no ícone ' > ', ao fazer isso,
-- abre-se abaixo do nome da tabela uma lista de componentes e o um dos itens deve ser
-- Trigger (ou Gatilho).
-- MARQUE o nome do gatilho e, novamente, clique na ABA SQL no painel da direita.
-- A seguir, copie/cole o conteúdo da aba SQL para o arquivo texto editado e já está com
-- a primeira parte da documentação.
-- A seguir o exemplo do conteúdo da aba SQL do gatilho vinculado na tabela.
--
-- Gatilho: BIclientes
-- Trigger: BIclientes

-- DROP TRIGGER IF EXISTS "BIclientes" ON public.clientes;

CREATE OR REPLACE TRIGGER "BIclientes"
    BEFORE INSERT
    ON public.clientes
    FOR EACH ROW
    EXECUTE FUNCTION public."clientesBI"();

-- GRAVE seu arquivo com estas documentações com o nome GATILHOnn.SQL onde nn é o numero do gatilho (com 2 digitos).
-- ########################################################################################################################################
-- TESTANDO: Escrevendo um comando de INSERT na tabela clientes para verificação do comportamento do gatilho e da função de gatilho.
-- Recuperando a strutura da tabela Clientes:
-- clientes [cpcliente,txnomecliente,txrazaosocial,celogradouro,
             txcomplemento,nucep,vllimitecompra,aosituacao,dtcadcliente]
-- 'Forçando' a inclusão de uma linha com valor de Chave Primária declarada.
-- O gatilho deve 'alterar' o valor da CP para o valor máximo + 5 unidades.
insert into clientes 
       values ('140',
               'Tratoria do Tenente',
               'Restaurante e Pizzaria Tratoria do Tenente S/C Ltda.',
               '10',
               '1450 - bloco 2',
               '12345678',
               20000,
               'A',
               '2025-04-10')
-- Verificnado a linha gravada.
select * from clientes order by cpcliente desc limit 1;
-- Comentário: o subcomando limit exibe a quantidade de linhas especificada.
-- 
-- 'Forçando' a inclusão de uma linha com valor de Chave Primária NULA.
-- Do mesmo modo, o gatilho deve 'alterar' o valor da CP para o valor máximo + 5 unidades.
insert into clientes 
       values (null,
               'Tratoria do Tenente',
               'Restaurante e Pizzaria Tratoria do Tenente S/C Ltda.',
               '10',
               '1450 - bloco 2',
               '12345678',
               20000,
               'A',
               '2025-04-10')
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 02 || Criação: 2025-1 | Revisão: 2026-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 
-- Esta série de arquivos demonstra o desenvolvimento de gatilhos.
-- Desde a fase de percepção do problema, passando pelo desenolvimento do algoritmo e implementação em PL/PGSQL do PostgreSQL.
-- 
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Desenvolva um gatilho que apresente o novo valor de uma
-- chave primária a cada linha inserida para a tabela autores.
-- Tabela: autores
-- Evento: INSERT
-- Quando: Antes do INSERT
-- 
-- Nome da função de gatilho: autoresBI
-- Nome do Gatilho: BIautores
--
-- Algoritmo:
-- ? Qual operação da AR (SQL) deve ser executada para apresentar o próximo valor de PKAUTOR a ser inserido na tabela?
-- Projeção: autores[[ MAX(pkautor)+10 -> vmax
-- SQL:
SELECT MAX(pkautor)+10 from autores;
-- a função MAX() pode retornar NULL se a tabela estiver vazia.
-- Então se o resultado da projeção for armazenado em variavel,
-- deve-se verificar se o valor retornado é NULL.
-- 
-- SE SIM então o valor a ser atribuído na pkautor deve ser 10.
--    SENÃO o valor da pk deve ser o que está armazendo na variável.
--
-- O Algoritmo
vmax smallint;
inicio
  vmax=valor máximo(pkautor de autores)+10;
  se vmax é nulo 
     então{ new.pkautor=10;
          }
     senão{ new.pkautor=vmax;
          }
  fim-do-se
  retorne nova-linha;
fim

-- Este algoritmo está bem próximo da linguagem pl/pgsql. Como deve ser.
-- 
-- Escreva o Gatilho em PostgreSQL (lembre-se: Trigger Function + Trigger.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 03 || Criação: 2025-1 | Revisão: 2025-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Exemplos de Gatilhos  || Criação: 2025-1 | Revisão: 2025-2
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 
-- Esta série de arquivos demonstra o desenvolvimento de gatilhos.
-- Desde a fase de percepção do problema, passando pelo desenolvimento do algoritmo e implementação em PL/PGSQL do PostgreSQL.
-- 
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Escreva uma TF e um T para determinar o número sequencial de uma CP de uma tabela da base de dados.
-- Este número sequencial deve ser incrementado em 5 unidades a cada linha Inserida na Tabela,
-- sempre sendo referenciado em relação ao último valor da CP da tabela.
-- Note:
   - O campo CP da tabela deverá ser do tipo Numérico.
   - Considere seu gatilho sendo executado mesmo em uma tabela sem tuplas.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Analisando: Tabela: idiomas, Momento: ANTES e o Evento: INSERT -> Isso DETERMINA o Nome da TF: idiomasbi()
-- - Note este nome é uma proposta é uma proposta. Entretanto, é boa prática seguir esta indicação.
-- O Nome de uma TF deve ser ÚNICO em uma Base de Dados.
-- Por isso é boa prática vincular o nome da TF ao nome da tabela, ao momento e ao evento.
-- 
-- PORTANTO, ANTES de INSERIR o registro em idiomas, deve-se:
-- Verificar qual é o máximo valor gravado da CP na tabela.
   -- usaremos o comando SQL, na forma:
      select MAX(cpidioma) as mreg from idiomas; -- Linha sem comentário! TESTE NO PGAdmin 4 (QueryTool).
-- Temos que registrar este em uma VARIÁVEL.
   -- Em PLPGSQL (Procedural Language from PostgreSQL) usamos
   -- o DECLARE com o nome e tipo de dado.
   -- Os tipos de dados de variáveis são os mesmos dos campos de tabelas.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Algoritmo da TF.
Declarar variavel: vmax integer;
InicioBloco
  vmax <- 'Ler' o máximo valor do campo cpidioma idiomas;
  SE ( vmax é NULO )
     ENTÃO
       NEW.cpidioma <- 5;
     SENÃO
       NEW.cpidioma <- vmax + 5;
  FimSe;
  Retorne a nova linha;
FimBloco;
--
-- traduzindo para plpgsql
--
declare
   vmax integer;
begin
   select MAX(cpidioma) into vmax from idiomas;
   if ( vmax is null )
   then
      NEW.cpidioma:= 5;
   else
      NEW.cpidioma:= vmax + 5;
   end if;
   return new;
end;
-- O segmento de código pode ser implementado como TRIGGER FUNCTION no PostgreSQL. 
-- DEPOIS de escrever uma Trigger Function temos que 'vincular' a TF na tabela alvo.
--  Devemos escrever uma TRIGGER na tabela.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Seguimos com a implementação.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Função de Gatilho: idiomasbi()
-- FUNCTION: public.idiomasbi()
-- DROP FUNCTION IF EXISTS public.idiomasbi();

CREATE OR REPLACE FUNCTION public.idiomasbi()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
   vmax integer;
begin
   select MAX(ididioma) into vmax from idiomas;
   if ( vmax is null )
   then
      NEW.ididioma:= 5;
   else
      NEW.ididioma:= vmax + 5;
   end if;
   return new;
end;
$BODY$;

ALTER FUNCTION public.idiomasbi() OWNER TO postgres;
COMMENT ON FUNCTION public.idiomasbi() IS 'determina o próximo valor da CP da tabela';
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Gatilho da tabela que dispara o idiomasbi()
-- Trigger: biidiomas
-- DROP TRIGGER IF EXISTS biidiomas ON public.idiomas;
CREATE OR REPLACE TRIGGER biidiomas
    BEFORE INSERT
    ON public.idiomas
    FOR EACH ROW
    EXECUTE FUNCTION public.idiomasbi();
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Agora podemos TESTAR a eficácia da Função de Gatilho e do Gatilho executando 'INSERTs' em idiomas.
select * from idiomas;
insert into idiomas
       values (20,
               'Português - Brasil',
               'Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos',
               '2024-04-18');
-- O comando insert acima 'força' o valor da CP (ididoma) para '20'; entretanto, a Função de Gatilho retorna o valor 5
-- para a ididioma pois a tabela estava sem tuplas (vazia).
-- Executando outros inserts
insert into idiomas values (NULL,
                            'inglês - americano',
                            'Idioma inglês com expressões usadas nos USA',
                            '2024-04-18'),
                           (null,
                            'inglês - americano',
                            'Idioma inglês com expressões usadas na Grã-Bretanha',
                            '2024-04-18'),
                           (null,
                            'espanhol - europeu',
                            'Idioma de origem latina com derivação em vários paises',
                            '2024-04-18');
-- 
-- Direcionando o comando de leitura de todas as linhas de idiomas para um arquivo
copy idiomas to '/tmp/idiomas.txt';
-- Tem-se os seguintes registros:
5	Português - Brasil	Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos	2024-04-18
10	inglês - americano	Idioma inglês com expressões usadas nos USA	2024-04-18
15	inglês - americano	Idioma inglês com expressões usadas na Grã-Bretanha	2024-04-18
20	espanhol - europeu	Idioma de origem latina com derivação em vários paises	2024-04-18

-- melhorando a apresentação dos dados com
copy idiomas to '/tmp/idiomas.txt' using delimiters '|';
-- Tem-se, então:
5 |Português - Brasil|Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos|2024-04-18
10|inglês - americano|Idioma inglês com expressões usadas nos USA|2024-04-18
15|inglês - americano|Idioma inglês com expressões usadas na Grã-Bretanha|2024-04-18
20|espanhol - europeu|Idioma de origem latina com derivação em vários paises|2024-04-18
--
-- Note os valores da ididioma que foram geradas pela Função de Gatilho.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 03 || Criação: 2025-1 | Revisão: 2026-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 
-- Proposição:
-- Desenvolva um gatilho (uma TF - Trigger Function + Trigger) que implemente o valor de um campo Chave Primária em
-- uma tabela atribuindo o valor incrementado em 5 unidades em relação ao máximo valor da CP da tabela.
-- SE a tabela estiver vazia o primeiro valor deve ser atribuído com valor 5.
-- Determinando o nome do gatilho:
-- Escolha uma tabela: 
-- Eventos: INSERT de linhas na tabela e
-- Momentos: ANTES da inserção da linha na tabela
--
-- Nome: pedvendasbi()
-- Algoritmizando:
declare
   vreg smallint;
InicioBloco
   mreg <- pesquisar o ultimo valor da cp gravado; **Cuidado** Muitos DBA encontram esta especificação de pesquisa,
           MAS nem todo o SGBD responde isso facilmente. Lembre-se da analogia:
           uma tabela é uma cômoda de roupas com gavetas que podem ser 'acrescentadas'
           (aumentando a altura) ou com gavetas vazias (que tiveram seu conteúdo DELETADO)
           que são 'preenchidas' pelo SGBD quando se fazem novos 'INSERTs' na tabela.
           TALVEZ o melhor 'entendimento' desta especificação seja esta:
           [determinar o máximo valor gravado no campo que é a chave primária da tabela],
           Para responder isso, pode-se usar a função MAX()
         - Disponível em mais de 90% dos SGBDs.
   SE um DBA quiser documentar o gatilho pode escrever o comando SQL que resolve a especificação, assim:
   select MAX(campo) into vreg from tabela; 
   SE ( mreg é nulo ) ** Cuidado ** verifique qual o retorno da função MAX() no caso de processar um valor NULO.
      Aparentemente, à partir da versão 12 do PostgreSQL o processamento de função ao retornar NULO indica o valor ZERO
      para o retorno da função.
      ENTÃO
         NEW.cppedvenda <- 5;
      SENÃO
         NEW.cppedvenda <- mreg + 5;
   FIM SE;
   retorna NEW;
FimBloco;

-- construindo a TF em plpgsql - procedural language of PostGreSQL
declare
   mreg smallint;
begin
  select MAX(cppedvenda) INTO mreg from pedvendas;
  if ( mreg is null )
  then
     NEW.cppedvenda := 5;
  else
     NEW.cppedvenda := mreg + 5;
  end if;
  return new;
end;
-- 
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 04 || Criação: 2025-1 | Revisão: 2026-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 
-- Proposição:
-- Desenvolva um gatilho (uma TF - Trigger Function + Trigger) que implemente o valor de um campo Chave Primária em
-- uma tabela atribuindo o valor incrementado em 5 unidades em relação ao máximo valor da CP da tabela.
-- SE a tabela estiver vazia o primeiro valor deve ser atribuído com valor 5.
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- Nome da tabela: AUTORIAS
-- FUNCTION: public.autoriasBIU()

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

CREATE OR REPLACE FUNCTION public."autoriasBIU"()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
  vdias smallint;
  vMAXIMO smallint;
begin
  select max(idautoria)+10 into vMAXIMO from autorias;
  IF vMAXIMO IS NULL
  THEN
    NEW.idautoria:=10;
  ELSE
    NEW.idautoria:=vMAXIMO;
  END IF;
  -- Até aqui se verificou o proximo valor da chave primária.
  -- Agora vamos verificar se o autor teve outra publicação em menos de 2 anos.
  select current_date-MAX(dtpublicacao) into vdias
         from livros inner join autorias
                     on livros.idlivro=autorias.livroid
         where autorias.autorid=new.autorid
         group by autorias.autorid
         order by autorias.autorid;
  if ( vdias<731 )
  then
    raise exception 'Autor (%) publicou a menos de 2 anos.',new.autorid;
    return null;
else
    return new;
  end if;
end;
$BODY$;

ALTER FUNCTION public."autoriasBIU"() OWNER TO postgres;
COMMENT ON FUNCTION public."autoriasBIU"() IS 'Determina o próximo valor da CP. A seguir, verifica se o autor fez alguma publicação à menos de dois anos.';
-- Escrevendo o Gatilho na tabela autorias.
-- Trigger: BIUautorias

-- DROP TRIGGER IF EXISTS "BIUautorias" ON public.autorias;

CREATE OR REPLACE TRIGGER "BIUautorias"
    BEFORE INSERT OR UPDATE 
    ON public.autorias
    FOR EACH ROW
    EXECUTE FUNCTION public."autoriasBIU"();
-- Testando a Função de Gatilho e Gatilho
-- verificando os dados em livros e autorias.
copy livros2020 TO '/tmp/livros+2020.txt' using delimiters '|';
copy autorias TO '/tmp/autorias.txt' using delimiters '|';
-- os arquivos foram editados e os registros são:
-- LIVROS:
idlivro,txtituloacervo,                nuisbn,editoraid,dtpublicacao,nuanopublicacao,qtpaginas,qtexemplaresacervo,qtexemplaresconsulta,dtcadlivro
     10|Preparando pratos com Lagostas|\N    |        1|2023-10-12  |           2010|       \N|                20|                   2|2011-08-01
    100|A Menina que roubava Livros   |\N    |       \N|2024-04-10  |           2007|       \N|                75|                  10|2007-05-10

-- AUTORIAS:
idautoria, livroid, autorid, dtcadautoria
5        |      10|      10| 2011-10-10
10       |      10|      20| 2010-10-10
15       |     110|      10| 2019-09-21

-- Executando:
insert into autorias values (null, 100, 10,'2024-04-18');

-- Tem-se a resposta do servidor:
ERROR:  Autor (10) publicou a menos de 2 anos.
CONTEXT:  função PL/pgSQL "autoriasBIU"() linha 23 em RAISE 
SQL state: P0001

-- A resposta da Função de Gatilho pode ser tratada na camada CGI por diversas linguagens de programação.
-- 
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 05 || Criação: 2025-1 | Revisão: 2026-2 ------------------------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------------------------------------
-- 
-- Trigger Function()
-- --------------------------------------------------------------------------------------
-- FUNCTION: public.pedvendabi()
-- DROP FUNCTION IF EXISTS public.pedvendabi();
CREATE OR REPLACE FUNCTION public.pedvendabi()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
   mreg smallint;
begin
  select MAX(cppedvenda) INTO mreg from pedvendas;
  if ( mreg is null )
  then
     NEW.cppedvenda := 5;
  else
     NEW.cppedvenda := mreg + 5;
  end if;
  return new;
end;
$BODY$;

ALTER FUNCTION public.pedvendabi()
    OWNER TO postgres;
-- --------------------------------------------------------------------------------------
Gatilho na Tabela
-- --------------------------------------------------------------------------------------
-- Trigger: bipedvendas

-- DROP TRIGGER IF EXISTS bipedvendas ON public.pedvendas;

CREATE OR REPLACE TRIGGER bipedvendas
    BEFORE INSERT
    ON public.pedvendas
    FOR EACH ROW
    EXECUTE FUNCTION public.pedvendabi();