Boa Noite a todos,
Eu utilizei a TABELA_FUNCIONARIO e a TABELA_PROJETO para deixar mais dificultoso o exercício. O meu foco foi utilizar ambas as tabelas para atualizar a minha TABELA_BUDGET que será a tabela que receberá o DEPARTAMENTO, VALOR e a TRIGGER em si. A TABELA_DEPARTAMENTO eu utilizei apenas para acrescentar um novo departamento e testar nas outras.
SELECT
CODIGO_DEPARTAMENTO, COUNT (NOME_FUNCIONARIO) AS TOTAL_FUNCIONARIO
FROM TABELA_FUNCIONARIO TF
GROUP BY CODIGO_DEPARTAMENTO;
SELECT CODIGO_DEPARTAMENTO, SUM(ORCAMENTO) AS ORCAMENTO_POR_DEPARTAMENTO
FROM TABELA_PROJETO TP
GROUP BY CODIGO_DEPARTAMENTO;
Após isso fiz um INNER JOIN para juntar essas duas tabelas e ter uma subquery para utilizar na TABELA_BUDGET.
SELECT
TP.CODIGO_DEPARTAMENTO AS DEPARTAMENTO, TF.TOTAL_FUNCIONARIO,
TP.ORCAMENTO_POR_DEPARTAMENTO / TF.TOTAL_FUNCIONARIO AS VALOR
FROM
(SELECT
TF.CODIGO_DEPARTAMENTO, COUNT (NOME_FUNCIONARIO) AS TOTAL_FUNCIONARIO
FROM TABELA_FUNCIONARIO TF
GROUP BY CODIGO_DEPARTAMENTO) TF
INNER JOIN
(SELECT TP.CODIGO_DEPARTAMENTO, SUM(ORCAMENTO) AS ORCAMENTO_POR_DEPARTAMENTO
FROM TABELA_PROJETO TP
GROUP BY CODIGO_DEPARTAMENTO) TP
ON TF.CODIGO_DEPARTAMENTO = TP.CODIGO_DEPARTAMENTO;
Após isso criei a TABELA_BUDGET que irei utilizar para agregar os campos departamento e valor, e fiz um SELECT para verificação
CREATE TABLE TABELA_BUDGET
(DEPARTAMENTO VARCHAR2(10) NULL,
VALOR NUMBER NULL);
SELECT * FROM TABELA_BUDGET;
Então fiz a lógica por trás da TABELA_BUDGET para que sempre que atualizarmos ou a TABELA_FUNCIONARIO com um funcionário novo ou a TABELA_PROJETO com um orçamento novo vai atualizar a TABELA_BUDGET, porém é a lógica sem a TRIGGER, processo ainda manual.
DELETE FROM TABELA_BUDGET;
INSERT INTO TABELA_BUDGET (DEPARTAMENTO, VALOR)
SELECT
TP.CODIGO_DEPARTAMENTO AS DEPARTAMENTO,
TP.ORCAMENTO_POR_DEPARTAMENTO / NULLIF(TF.TOTAL_FUNCIONARIO,0) AS VALOR
FROM
(SELECT
TF.CODIGO_DEPARTAMENTO, COUNT (NOME_FUNCIONARIO) AS TOTAL_FUNCIONARIO
FROM TABELA_FUNCIONARIO TF
GROUP BY CODIGO_DEPARTAMENTO) TF
INNER JOIN
(SELECT TP.CODIGO_DEPARTAMENTO, SUM(ORCAMENTO) AS ORCAMENTO_POR_DEPARTAMENTO
FROM TABELA_PROJETO TP
GROUP BY CODIGO_DEPARTAMENTO) TP
ON TF.CODIGO_DEPARTAMENTO = TP.CODIGO_DEPARTAMENTO;
Por fim a parte mais complicada, bati a cabeça, porque tentei criar uma TRIGGER para as duas tabelas, porém vi que não era viável, depois mesmo tentando criar uma de cada utilizando os mesmos códigos, bati cabeça e descobri que estava ocorrendo um evento chamado TABELA MUTANTE, que é quando tentamos usar a mesma tabela que vai alimentar a TRIGGER no processo dentro da Subquery. Nesse momento fiz minhas pesquisas a parte atráves de Youtube, IA, Sites do Google e descobri as funções COMPOUND TRIGGER que fez uma tabela composta para mim e a função AFTER STATEMENT IS que faz com que todo o processo da Subquery seja lido para depois ela atuar, assim acabando com a TABELA MUTANTE e fazendo minhas TRIGGERS funcionarem. Adquiri muito conhecimento nesse exercício devido essa situação. Segue os códigos das duas TRIGGERS.
Trigger para a TABELA_FUNCIONARIO
CREATE OR REPLACE TRIGGER TG_TABELA_BUDGET_FUNCIONARIO
FOR INSERT OR UPDATE OR DELETE ON TABELA_FUNCIONARIO
COMPOUND TRIGGER
AFTER STATEMENT IS
BEGIN
DELETE FROM TABELA_BUDGET;
INSERT INTO TABELA_BUDGET (DEPARTAMENTO, VALOR)
SELECT
TP.CODIGO_DEPARTAMENTO AS DEPARTAMENTO,
TP.ORCAMENTO_POR_DEPARTAMENTO / NULLIF(TF.TOTAL_FUNCIONARIO,0) AS VALOR
FROM
(SELECT
TF.CODIGO_DEPARTAMENTO, COUNT (NOME_FUNCIONARIO) AS TOTAL_FUNCIONARIO
FROM TABELA_FUNCIONARIO TF
GROUP BY CODIGO_DEPARTAMENTO) TF
INNER JOIN
(SELECT TP.CODIGO_DEPARTAMENTO, SUM(ORCAMENTO) AS ORCAMENTO_POR_DEPARTAMENTO
FROM TABELA_PROJETO TP
GROUP BY CODIGO_DEPARTAMENTO) TP
ON TF.CODIGO_DEPARTAMENTO = TP.CODIGO_DEPARTAMENTO;
END AFTER STATEMENT;
END TG_TABELA_BUDGET_FUNCIONARIO;
/
CREATE OR REPLACE TRIGGER TG_TABELA_BUDGET_PROJETO
FOR INSERT OR UPDATE OR DELETE ON TABELA_PROJETO
COMPOUND TRIGGER
AFTER STATEMENT IS
BEGIN
DELETE FROM TABELA_BUDGET;
INSERT INTO TABELA_BUDGET (DEPARTAMENTO, VALOR)
SELECT
TP.CODIGO_DEPARTAMENTO AS DEPARTAMENTO,
TP.ORCAMENTO_POR_DEPARTAMENTO / NULLIF(TF.TOTAL_FUNCIONARIO,0) AS VALOR
FROM
(SELECT
TF.CODIGO_DEPARTAMENTO, COUNT (NOME_FUNCIONARIO) AS TOTAL_FUNCIONARIO
FROM TABELA_FUNCIONARIO TF
GROUP BY CODIGO_DEPARTAMENTO) TF
INNER JOIN
(SELECT TP.CODIGO_DEPARTAMENTO, SUM(ORCAMENTO) AS ORCAMENTO_POR_DEPARTAMENTO
FROM TABELA_PROJETO TP
GROUP BY CODIGO_DEPARTAMENTO) TP
ON TF.CODIGO_DEPARTAMENTO = TP.CODIGO_DEPARTAMENTO;
END AFTER STATEMENT;
END TG_TABELA_BUDGET_PROJETO;
/
OBS: Também aprendi nessas pesquisas ali na parte que o Orçamento esta dividindo o número de funcionários a utilizar a função NULLIF, da forma que eu fiz , mesmo se tivermos 0 funcionários ou 0 projetos a Trigger não irá quebrar.
Obrigado a todos.
