Após este artigo, você será capaz de:
- Entender Tabelas Mutantes
- Resolver Problemas de Tabelas Mutantes
O que é uma Tabela Mutante?
Imagine que uma tabela no banco de dados é como uma grande planilha. Quando você faz uma alteração nessa planilha (como adicionar, atualizar ou excluir uma linha), é como se a planilha estivesse “em obras”. Durante esse momento de mudança, chamamos essa tabela de “mutante”.
Por que isso é um problema?
O problema surge quando um trigger (uma espécie de alarme automático do banco de dados) tenta ler ou modificar essa tabela enquanto ela está “em obras”. É como se você tentasse ler um livro enquanto alguém está arrancando ou colando páginas nele!
Um exemplo do cotidiano
Pense numa fila de supermercado. Você está na fila (que representa a tabela) e decide trocar de fila (uma operação de UPDATE). Agora, imagine que exista uma regra: se a nova fila tiver mais de 10 pessoas, você não pode entrar nela. Para verificar isso, você precisa contar quantas pessoas estão na fila. Mas e se, exatamente nesse momento, outras pessoas estiverem entrando ou saindo da fila? Sua contagem seria confusa e possivelmente incorreta. Essa é a essência do problema das tabelas mutantes!
O exemplo técnico explicado
Considere o seguinte exemplo de um trigger causando um erro de tabela mutante:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE OR REPLACE TRIGGER employees_biu BEFORE INSERT OR UPDATE ON employees FOR EACH ROW DECLARE v_total NUMBER; v_name VARCHAR2(100); BEGIN SELECT COUNT(*) INTO v_total FROM employees -- EMPLOYEES está MUTANDO WHERE department_id = :NEW.department_id; -- verifica se o departamento atual está com excesso de pessoal IF v_total >= 45 THEN SELECT department_name INTO v_name FROM departments WHERE department_id = :NEW.department_id; RAISE_APPLICATION_ERROR (-20000, 'Departamento '||v_name||' está com excesso de pessoal'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Este não é um departamento válido'); END; |
Este trigger dispara antes de uma instrução INSERT ou UPDATE ser emitida na tabela EMPLOYEES. O trigger verifica se o departamento especificado tem muitos funcionários. Se o número de funcionários em um departamento for igual ou maior que 45, o trigger emite uma mensagem de erro indicando que o departamento está com excesso de pessoal.
Agora, considere a seguinte instrução UPDATE emitida contra a tabela EMPLOYEES:
|
1 2 3 |
UPDATE employees SET department_id = 60 WHERE employee_id = 103; |
Quando esta instrução UPDATE é emitida contra a tabela EMPLOYEES, a seguinte mensagem de erro é exibida:
|
1 2 3 4 |
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it ORA-06512: at "HR.EMPLOYEES_BIU", line 5 ORA-04088: error during execution of trigger 'HR.EMPLOYEES_BIU' |
Observe que a mensagem de erro afirma que a tabela EMPLOYEES está mutante e o trigger não pode vê-la. Esta mensagem de erro é gerada porque há uma instrução SELECT INTO, emitida contra a tabela EMPLOYEES que está sendo modificada e, portanto, está mutante.
|
1 2 3 4 |
SELECT COUNT(*) INTO v_total FROM employees WHERE department_id = :NEW.department_id; |
Por que o erro ocorre?
Quando você executa:
|
1 2 3 |
UPDATE employees SET department_id = 60 WHERE employee_id = 103; |
É como se você estivesse movendo um funcionário para outro departamento. O trigger tenta contar quantos funcionários já estão nesse departamento, mas a tabela está “em obras” (mutando) porque você está fazendo uma alteração nela.
A mensagem de erro decodificada
Quando o banco de dados diz:
|
1 |
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it |
Ele está basicamente gritando: “Ei, não posso contar os funcionários agora, a tabela está mudando!”
Resolvendo Problemas de Tabelas Mutantes
Para corrigir o erro de tabela mutante descrito anteriormente, os seguintes passos devem ser tomados ao usar uma versão do Oracle anterior à 11g:
- Para registrar o ID do departamento e o nome conforme descrito no exemplo anterior, duas variáveis globais devem ser declaradas com a ajuda de um pacote PL/SQL.
- O trigger existente deve ser modificado para que ele registre o ID do departamento, consulte a tabela DEPARTMENTS e registre o nome do departamento.
- Um novo trigger deve ser criado na tabela EMPLOYEES. Este trigger deve ser um trigger de nível de instrução que dispara após a instrução INSERT ou UPDATE ter sido emitida. Ele verificará o número de funcionários em um determinado departamento e gerará um erro se esse número for igual ou maior que 45.
Considere a especificação do pacote a seguir:
|
1 2 3 4 5 6 |
CREATE OR REPLACE PACKAGE department_adm AS g_department_id departments.department_id%TYPE; g_department_name departments.department_name%TYPE; END; / |
Esta especificação de pacote contém declarações para duas variáveis globais, g_department_id e g_department_name.
Em seguida, o trigger EMPLOYEES_BIU existente é modificado da seguinte forma:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE OR REPLACE TRIGGER employees_biu BEFORE INSERT OR UPDATE ON employees FOR EACH ROW BEGIN IF :NEW.department_id IS NOT NULL THEN BEGIN -- Assign new department ID to the global variable department_adm.g_department_id := :NEW.department_id; SELECT department_name INTO department_adm.g_department_name FROM departments WHERE department_id = department_adm.g_department_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid department'); END; END IF; END; / |
Finalmente, um novo trigger de nível de instrução é criado na tabela EMPLOYEES:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE OR REPLACE TRIGGER employees_aiu AFTER INSERT OR UPDATE ON employees DECLARE v_total INTEGER; BEGIN SELECT COUNT(*) INTO v_total FROM employees WHERE department_id = department_adm.g_department_id; -- check if the current department is overstaffed IF v_total >= 45 THEN RAISE_APPLICATION_ERROR (-20000, 'Department '||department_adm.g_department_name||' is overstaffed'); END IF; END; / |
Este trigger dispara após uma instrução INSERT ou UPDATE ser emitida contra a tabela EMPLOYEES. Ele verifica o número de funcionários em um determinado departamento e gera um erro se esse número for igual ou maior que 45.
Como resultado, a instrução UPDATE usada anteriormente produzirá o erro ORA-20000 conforme esperado:
|
1 2 3 |
UPDATE employees SET department_id = 50 WHERE employee_id = 107; |
|
1 2 3 4 |
RA-20000: Department Shipping is overstaffed ORA-06512: at "HR.EMPLOYEES_AIU", line 12 ORA-04088: error during execution of trigger 'HR.EMPLOYEES_AIU' |
Este erro é gerado pelo trigger EMPLOYEES_AIU e não contém nenhuma mensagem sobre uma tabela mutante.
Agora considere uma instrução UPDATE similar para um departamento diferente que não causa nenhum erro:
|
1 2 3 |
UPDATE employees SET department_id = 20 WHERE employee_id = 107; |
Esta abordagem resolve o problema da tabela mutante, permitindo que você implemente a lógica de negócios desejada sem violar as restrições do Oracle sobre tabelas mutantes.
Considerações Adicionais sobre Tabelas Mutantes
Compound Triggers (Oracle 11g e posterior)
A partir do Oracle 11g, uma solução mais elegante para o problema de tabelas mutantes é o uso de compound triggers. Um compound trigger permite definir diferentes seções de código para diferentes momentos de disparo em um único trigger.
Exemplo de um compound trigger que resolve o problema de tabela mutante:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
CREATE OR REPLACE TRIGGER employees_comp_trg FOR INSERT OR UPDATE ON employees COMPOUND TRIGGER -- Global variables for the trigger TYPE dept_count_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER; g_dept_count dept_count_t; -- Before statement section BEFORE STATEMENT IS BEGIN -- Initialize the associative array FOR dept IN (SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id) LOOP g_dept_count(dept.department_id) := dept.emp_count; END LOOP; END BEFORE STATEMENT; -- Before each row section BEFORE EACH ROW IS BEGIN -- Update the count for the affected departments IF :OLD.department_id IS NOT NULL THEN g_dept_count(:OLD.department_id) := g_dept_count(:OLD.department_id) - 1; END IF; IF :NEW.department_id IS NOT NULL THEN IF g_dept_count.EXISTS(:NEW.department_id) THEN g_dept_count(:NEW.department_id) := g_dept_count(:NEW.department_id) + 1; ELSE g_dept_count(:NEW.department_id) := 1; END IF; END IF; END BEFORE EACH ROW; -- After each row section AFTER EACH ROW IS BEGIN -- Check if the department is overstaffed IF :NEW.department_id IS NOT NULL AND g_dept_count(:NEW.department_id) > 45 THEN RAISE_APPLICATION_ERROR(-20000, 'Department ' || :NEW.department_id || ' is overstaffed'); END IF; END AFTER EACH ROW; END employees_comp_trg; / |
Este compound trigger resolve o problema de tabela mutante de forma mais eficiente:
- Na seção BEFORE STATEMENT, inicializamos um array associativo com a contagem atual de funcionários por departamento.
- Na seção BEFORE EACH ROW, atualizamos as contagens conforme as linhas são modificadas.
- Na seção AFTER EACH ROW, verificamos se o departamento excedeu o limite de funcionários.
Contexto de Sistema
Outra abordagem para resolver problemas de tabelas mutantes é usar o contexto do sistema. Esta técnica pode ser útil quando você precisa compartilhar informações entre diferentes triggers ou entre triggers e aplicações.
Exemplo usando contexto de sistema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
-- Criar o contexto CREATE OR REPLACE CONTEXT emp_ctx USING emp_ctx_pkg; -- Criar o pacote para gerenciar o contexto CREATE OR REPLACE PACKAGE emp_ctx_pkg IS PROCEDURE set_dept_count(p_dept_id IN NUMBER, p_count IN NUMBER); FUNCTION get_dept_count(p_dept_id IN NUMBER) RETURN NUMBER; END emp_ctx_pkg; / CREATE OR REPLACE PACKAGE BODY emp_ctx_pkg IS PROCEDURE set_dept_count(p_dept_id IN NUMBER, p_count IN NUMBER) IS BEGIN DBMS_SESSION.SET_CONTEXT('emp_ctx', 'dept_' || p_dept_id, p_count); END set_dept_count; FUNCTION get_dept_count(p_dept_id IN NUMBER) RETURN NUMBER IS v_count VARCHAR2(10); BEGIN v_count := SYS_CONTEXT('emp_ctx', 'dept_' || p_dept_id); RETURN NVL(TO_NUMBER(v_count), 0); END get_dept_count; END emp_ctx_pkg; / -- Trigger para inicializar e atualizar o contexto CREATE OR REPLACE TRIGGER employees_ctx_trg FOR INSERT OR UPDATE OR DELETE ON employees COMPOUND TRIGGER BEFORE STATEMENT IS BEGIN FOR dept IN (SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id) LOOP emp_ctx_pkg.set_dept_count(dept.department_id, dept.emp_count); END LOOP; END BEFORE STATEMENT; AFTER EACH ROW IS BEGIN IF INSERTING OR UPDATING THEN IF :NEW.department_id IS NOT NULL THEN emp_ctx_pkg.set_dept_count(:NEW.department_id, emp_ctx_pkg.get_dept_count(:NEW.department_id) + 1); END IF; END IF; IF DELETING OR UPDATING THEN IF :OLD.department_id IS NOT NULL THEN emp_ctx_pkg.set_dept_count(:OLD.department_id, emp_ctx_pkg.get_dept_count(:OLD.department_id) - 1); END IF; END IF; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN FOR dept IN (SELECT DISTINCT department_id FROM employees WHERE department_id IS NOT NULL) LOOP IF emp_ctx_pkg.get_dept_count(dept.department_id) > 45 THEN RAISE_APPLICATION_ERROR(-20000, 'Department ' || dept.department_id || ' is overstaffed'); END IF; END LOOP; END AFTER STATEMENT; END employees_ctx_trg; / |
Esta abordagem usa o contexto do sistema para armazenar e atualizar as contagens de funcionários por departamento, evitando assim o problema de tabela mutante.
Considerações de Desempenho
Ao lidar com tabelas mutantes, é importante considerar o impacto no desempenho:
- Compound Triggers: Geralmente oferecem melhor desempenho, pois permitem que você faça mais trabalho em uma única passagem pelos dados.
- Contexto de Sistema: Pode ter um pequeno overhead devido às chamadas de função para definir e obter valores do contexto.
- Pacotes com Variáveis Globais: Podem ser uma solução simples, mas podem não escalar bem em ambientes com muitos usuários concorrentes.
Melhores Práticas
- Minimize o Uso: Sempre que possível, projete seu esquema e lógica de negócios para evitar a necessidade de acessar tabelas mutantes em triggers.
- Use Compound Triggers: Se estiver usando Oracle 11g ou posterior, compound triggers geralmente são a melhor solução para problemas de tabelas mutantes.
- Considere Alternativas: Em alguns casos, pode ser melhor mover a lógica para procedimentos armazenados ou para a camada de aplicação.
- Teste Cuidadosamente: Certifique-se de testar suas soluções para tabelas mutantes com diferentes cenários, incluindo operações em lote e concorrência.
- Documente: Dado que as soluções para tabelas mutantes podem ser complexas, é crucial documentar bem a lógica e o raciocínio por trás da sua implementação.
Concluindo, lidar com tabelas mutantes é um desafio comum no desenvolvimento de triggers Oracle. As soluções apresentadas aqui – usando pacotes, compound triggers e contextos de sistema – oferecem diferentes abordagens para resolver este problema, cada uma com suas próprias vantagens e considerações. A escolha da melhor solução dependerá das necessidades específicas do seu aplicativo, da versão do Oracle que você está usando e dos requisitos de desempenho.
