🎯 ATIVIDADE 13 — O BANCO INTELIGENTE

Bem-vindo à décima terceira semana (4 aulas) do curso de Banco de Dados. Até agora, tratamos o banco de dados como um repositório passivo: a aplicação envia os dados e o banco apenas os guarda. Mas e se o próprio banco de dados pudesse tomar decisões, rodar códigos automáticos e proteger as regras de negócio? Hoje entraremos no mundo do Banco Inteligente, aprendendo a criar Stored Procedures (Procedimentos Armazenados) e Triggers (Gatilhos) para automatizar rotinas operacionais cruciais. 🛡️🤖

🎯 Objetivos de Aprendizagem do Laboratório

Ao final deste laboratório prático (estimativa: 4 horas presenciais / autoguiadas), você será capaz de:

• Compreender o conceito e os benefícios de programar dentro do SGBD.
• Criar e executar Stored Procedures e Functions no PostgreSQL e MySQL.
• Desenvolver Triggers (Gatilhos) automáticos para controle de eventos (INSERT, UPDATE, DELETE).
• Automatizar o controle de inventário (baixa automática de estoque) a cada venda registrada.

🏢 O Cenário Prático (Seu Desafio)

O departamento de logística da TecProExpress está preocupado com furos no estoque. Atualmente, quando um pedido é feito, a aplicação web é responsável por subtrair a quantidade vendida da tabela de produtos. Porém, falhas na aplicação às vezes pulam essa etapa, fazendo com que produtos sem estoque continuem a ser vendidos.

Seu desafio como Engenheiro de Dados é mover essa inteligência para o banco de dados. Você criará uma Trigger que, de forma 100% garantida e automática, subtrairá a quantidade comprada do estoque toda vez que um novo produto for inserido na tabela item_pedido.

🧠 Fundamentos: A Teoria Traduzida

O Gatilho Automático (Trigger)

Uma Trigger é como um sensor de presença com alarme: ela fica "vigiando" uma tabela. Se ocorrer um evento específico (como a inserção de uma linha), ela dispara um bloco de código (função) automaticamente antes ou depois da modificação.

flowchart TD
    A["Usuário insere linha em ITEM_PEDIDO"] --> B{"Trigger disparada?"}
    B -- SIM --> C["Executa Função de Ajuste de Estoque"]
    C --> D["Estoque do Produto é Subtraído"]
    D --> E["Linha é gravada fisicamente"]
    style B fill:#ffe0b2,stroke:#fb8c00
    style D fill:#c8e6c9,stroke:#4caf50

• OLD: Palavra-chave que dá acesso aos valores da linha antes da alteração (usado em Updates/Deletes).
• NEW: Palavra-chave que dá acesso aos novos valores que estão sendo gravados (usado em Inserts/Updates).

📖 Exemplo Guiado: Criando uma Stored Procedure

Vamos criar um procedimento para reabastecer o estoque de um produto de forma rápida.

1. Inicializando o Banco de Dados

CREATE DATABASE tecpro_automacao;
-- (Nota: No pgAdmin, abra a Query Tool apontando para o banco tecpro_automacao)

2. Criando o Cenário de Teste

CREATE TABLE estoque_produto (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    quantidade_estoque INT NOT NULL DEFAULT 0
);

INSERT INTO estoque_produto (nome, quantidade_estoque) VALUES 
('Capacete Pro', 10),
('Cabo Carregador', 50);

3. Criando a Procedure no PostgreSQL

CREATE OR REPLACE PROCEDURE adicionar_estoque(prod_id INT, qtd INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE estoque_produto 
    SET quantidade_estoque = quantidade_estoque + qtd 
    WHERE id = prod_id;
END;
$$;

-- Executando a Procedure para adicionar 20 capacetes
CALL adicionar_estoque(1, 20);

-- Verificando: Capacete Pro agora possui 30 unidades!
SELECT * FROM estoque_produto;

🛠️ Prática Obrigatória 1: Criando a Trigger de Estoque (PostgreSQL)

1. Crie a tabela pedido e a tabela item_pedido (seguindo as FKs para estoque_produto).
2. Crie uma Função de Trigger no PostgreSQL (linguagem plpgsql) que subtraia a quantidade comprada do estoque do produto correspondente na tabela estoque_produto usando o registro NEW.
3. Crie a Trigger associada à tabela item_pedido que dispara AFTER INSERT para cada linha inserida.

💻 Disparo da Trigger Automática & Logs no Terminal

Para verificar o abatimento automático de estoque acionado pelo gatilho SQL:

-- Inserindo item de pedido que aciona a TRIGGER 'trg_atualizar_estoque'
INSERT INTO item_pedido (id_pedido, id_produto, quantidade) VALUES (101, 1, 3);

-- Consultando estoque atualizado automaticamente:
SELECT nome, quantidade_estoque FROM estoque_produto WHERE id = 1;

🖥️ Saída Esperada no Terminal:

INSERT 0 1
-----------------------------------------------------------------
       nome       | quantidade_estoque 
------------------+--------------------
 Capacete Pro     |                 27
(1 row)
⚡ [TRIGGER] Quantidade reduzida de 30 para 27 via trigger 'trg_atualizar_estoque'.

🌐 Requisição cURL de Compra que Aciona a Trigger (Swagger /docs)

curl -X POST "http://127.0.0.1:8000/api/v1/pedidos/itens" \
     -H "Content-Type: application/json" \
     -d '{
       "pedido_id": 101,
       "produto_id": 1,
       "quantidade": 3
     }'

🔹 Resposta JSON:

{
  "mensagem": "Item adicionado ao pedido",
  "trigger_executada": true,
  "estoque_restante": 27
}

🛠️ Prática Obrigatória 2: Equivalência em MySQL

1. Pesquise a sintaxe de Trigger no MySQL (que não exige a criação de uma função separada, permitindo declarar o código diretamente no corpo do gatilho).
2. Escreva o script equivalente da Trigger de subtração de estoque adaptado para rodar no MySQL Workbench/DBeaver.

📤 Instruções de Entrega (Microsoft Teams)

Após validar suas automações:

1. Salve o script SQL completo contendo a criação das tabelas, da procedure, da função de trigger e da trigger física em um arquivo .sql (Ex: Atividade_13_SeuNome.sql).
2. Insira comentários explicando qual a diferença de comportamento entre uma Trigger disparada BEFORE (antes) e uma disparada AFTER (depois) do evento.
3. Envie o arquivo .sql correspondente no Microsoft Teams para avaliação.

💡 Checkpoint de Lógica

📊 Rubrica Formativa de Avaliação

🔥 Desafio de Fixação (Opcional)

Nível: Arquiteto de Banco de Dados 🏆

Modifique a Trigger para impedir a inserção de um item no pedido caso a quantidade solicitada seja maior do que a quantidade disponível em estoque. (Dica: Use RAISE EXCEPTION no PostgreSQL ou SIGNAL SQLSTATE no MySQL para barrar a inserção).

🔑 Gabarito de Código/Fórmulas Completo

🐘 Padrão PostgreSQL (pgAdmin)

-- 1. Criação das Tabelas Base
CREATE TABLE estoque_produto (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    quantidade_estoque INT NOT NULL DEFAULT 0
);

CREATE TABLE pedido (
    id SERIAL PRIMARY KEY,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE item_pedido (
    id_pedido INT REFERENCES pedido(id),
    id_produto INT REFERENCES estoque_produto(id),
    quantidade INT,
    PRIMARY KEY (id_pedido, id_produto)
);

INSERT INTO estoque_produto (nome, quantidade_estoque) VALUES ('Smartphone X', 10);
INSERT INTO pedido VALUES (1);

-- 2. Criação da Função de Trigger no Postgres
CREATE OR REPLACE FUNCTION processar_baixa_estoque()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE estoque_produto
    SET quantidade_estoque = quantidade_estoque - NEW.quantidade
    WHERE id = NEW.id_produto;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. Criação da Trigger vinculada
CREATE TRIGGER trg_baixa_estoque
AFTER INSERT ON item_pedido
FOR EACH ROW
EXECUTE FUNCTION processar_baixa_estoque();

-- 4. Teste Prático
INSERT INTO item_pedido (id_pedido, id_produto, quantidade) VALUES (1, 1, 3);

-- Verificação: O estoque deve ter baixado de 10 para 7!
SELECT * FROM estoque_produto;

🐬 Comparativo MySQL (Workbench / DBeaver)

-- No MySQL a trigger é declarada diretamente, sem necessidade de FUNCTION
DELIMITER $$

CREATE TRIGGER trg_baixa_estoque_mysql
AFTER INSERT ON item_pedido
FOR EACH ROW
BEGIN
    UPDATE estoque_produto
    SET quantidade_estoque = quantidade_estoque - NEW.quantidade
    WHERE id = NEW.id_produto;
END$$

DELIMITER ;

🔍 Explicação do Gabarito:

• NEW.quantidade: Dá acesso à quantidade que o usuário está inserindo na tabela item_pedido.
• FOR EACH ROW: Garante que se o usuário inserir 5 linhas de uma vez, a trigger rodará 5 vezes individualmente para dar baixa em cada produto.
• DELIMITER: No MySQL, altera o caractere de término de instrução (de ; para $$) para que o SGBD não ache que a Trigger terminou no primeiro ponto e vírgula interno.