Oracle Trigger

28 de novembro de 2012 5 respostas

ECO2004 28 de novembro de 2012

Eu tenho dois esquema de relação:

FUNCIONARIO (CPF. PNOME, SNOME, CPF_GERENTE, NOME_DEPARTAMENTO, SALARIO)
DEPARTAMENTO (NOME_DEPARTAMENTO, NUMERO_FUNCIONARIOS)

O que eu quero fazer é "ao inserir um novo funcionário de um departamento em FUNCIONARIO, soma-se um ao número que esteja em NUMERO_FUNCIONARIOS em DEPARTAMENTO". Assim, crio uma trigger em FUNCIONARIO:

create or replace trigger tri_fun_ndep after insert or update or delete on funcionario
for each row
declare
begin
    if updating then
    
      if ( :old.nome_departamento = 'TI' and :new.nome_departamento <> 'TI') then
        //update departamento set numero_funcionarios = :old.numero_funcionarios -1 
        //where nome_departamento = 'TI';
        
        update departamento set numero_funcionarios = (select numero_funcionarios from departamento
        where nome_departamento = :new.nome_departamento) - 1 where nome_departamento = 'TI';
      end if;
    end if;
end;

A linha comentada é como eu gostaria de fazer, pois é mais simples. Simplesmente pegar o valor antigo que se encontra em NUMERO_FUNCIONARIOS em DEPARTAMENTO, subtrair de um e depois salvar. Não posso fazer isso porque a trigger diz que o contexto global é a tabela FUNCIONARIO, gerando o erro:

Erro(39,55): PLS-00049: variável de ligação 'OLD.NUMERO_FUNCIONARIOS' inválida

Uma maneira de fazer funcionar se encontra nas linhas 11 e 12 acima.

Como é que eu posso pegar o valor antigo e novo da tabela DEPARTAMENTO sem fazer o que fiz?

5 Respostas

AbelBueno 28 de nov. de 2012

Acho que seria mais simples (e mais seguro) recontar todos os funcionários de ambos departamentos.
Algo como:

UPDATE departamento SET numero_funcionarios = ( SELECT COUNT(*) FROM funcionarios WHERE departamento = old.departamento ) WHERE departamento = old.departamento;
  UPDATE departamento SET numero_funcionarios = ( SELECT COUNT(*) FROM funcionarios WHERE departamento = new.departamento ) WHERE departamento = new.departamento;

Aliás, por que está armazenando o número de funcionários na tabela departamento?
Teve problemas de performance para isso?

Ao invés de usar uma trigger, que geralmente é melhor usar com muita moderação, poderia criar uma view com esse count online.

ECO2004 28 de nov. de 2012

AbelBueno:
Acho que seria mais simples (e mais seguro) recontar todos os funcionários de ambos departamentos.
Algo como:
UPDATE departamento SET numero_funcionarios = ( SELECT COUNT(*) FROM funcionarios WHERE departamento = old.departamento ) WHERE departamento = old.departamento;
  UPDATE departamento SET numero_funcionarios = ( SELECT COUNT(*) FROM funcionarios WHERE departamento = new.departamento ) WHERE departamento = new.departamento;
Aliás, por que está armazenando o número de funcionários na tabela departamento?
Teve problemas de performance para isso?

Ao invés de usar uma trigger, que geralmente é melhor usar com muita moderação, poderia criar uma view com esse count online.

Se a cada inserção eu tiver que contar a tabela inteira, um overhead será criado. A cada inserção de funcionário, já é somado um ao valor que se encontra em NUMERO_FUNCIONARIOS em DEPARTAMENTO.

Agora, tem como eu acessar a o valor “old” de NUMERO_FUNCIONARIOS de DEPARTAMENTO sem um SELECT, como abaixo? Lembrando que o comando abaixo gera erro.

update departamento set numero_funcionarios = :old.numero_funcionarios -1 where nome_departamento = 'TI';

AbelBueno 28 de nov. de 2012

Já existe o overhead do update. O banco costuma ser “esperto” o suficiente para contar registros.
Experimente medir os tempos.

Você não precisa do old, pode usar o valor do campo normalmente.

UPDATE departamento SET numero_funcionario = numero_funcionario + 1 WHERE departamento = new.departamento;

ECO2004 28 de nov. de 2012

AbelBueno:
ECO2004:

Se a cada inserção eu tiver que contar a tabela inteira, um overhead será criado. A cada inserção de funcionário, já é somado um ao valor que se encontra em NUMERO_FUNCIONARIOS em DEPARTAMENTO.

Já existe o overhead do update. O banco costuma ser “esperto” o suficiente para contar registros.
Experimente medir os tempos.

ECO2004:

Agora, tem como eu acessar a o valor “old” de NUMERO_FUNCIONARIOS de DEPARTAMENTO sem um SELECT, como abaixo? Lembrando que o comando abaixo gera erro.

UPDATE departamento SET numero_funcionario = numero_funcionario + 1 WHERE departamento = new.departamento;

@Abel

Eu estou recebendo um erro de tabela mutante no código abaixo - linha 04.

if deleting then
    
      update departamento set numero_funcionarios = numero_funcionarios - 1 
      where nome_departamento = :old.nome_departamento;
      
      update historico set data_desligamento = sysdate where cpf = :old.cpf;
      
end if;

A minha dúvida:

Quando uma linha é apagada em FUNCIONARIO, o valor de qualquer campo dessa linha pode ser pega com :old, não é? O meu trigger é disparado quando uma determinada linha é apagada. Eu quero saber o valor do CPF que essa linha recém apagada detinha e usar para atualizar o NUMERO_FUNCIONARIOS em DEPARTAMENTO.

Há algo de errado com minha sintaxe e porque está dando erro de tabela mutante? Não vejo acesso à mesma tabela onde o trigger está atuando.

ECO2004 29 de nov. de 2012

Alguém?

Criado 28 de novembro de 2012

Ultima resposta 29 de nov. de 2012

Respostas 5

Participantes 2

5 Respostas

Topicos relacionados