Mysql, Trigger, Recursiva

[Fabricio Fachini]

Oi, preciso da sua ajuda para esclarecer o que relato a seguir:

Tenho duas tabelas: Clientes e controle.

Ao alterar a tabela clientes, disparo uma trigger que atualiza a tabela controle; a tabela controle, por sua vez, tem uma trigger que atualiza uma coluna na tabela cliente.

O seguinte erro ocorre:

Can´t update table <nome da tabela> in stored function/trigger because it is used by statement which invoked this stored function/trigger

Pesquise na net e algumas fontes indicam que este tipo de ação não é permitido em Mysql. Como as fontes são referente a versões mais antigas do Mysql(<5) e estou usando a versão 5.0.4, resolvi postar esta pergunta.

Obrigado pela atenção

Fabricio Fachini

[Denis Courcy]

Oi, Fabricio Fachini!

Realmente o MySQl não possui este recurso, ainda. Ele entende que isto é recursividade e bloqueia a ação.

[Fabricio Fachini]

Obrigado Denis pela resposta!

você indica alguma solução alternativa para que possa realizar esta ação?

Mais uma vez, obrigado!

Fabricio Fachini

[Denis Courcy]

Obrigado Denis pela resposta!

você indica alguma solução alternativa para que possa realizar esta ação?

Mais uma vez, obrigado!

Fabricio Fachini

Experimente colocar no primeiro trigger o código que estava denntro do segundo trigger após a atualização da segunda tabela.

Em tese ficaria assim:

trigger dentro da priemeira tabela
   atualiza segunda tabela
   atualiza primeira tabela com dados da segunda tabela.

Outra alternativa seria por SP (storage procedure)

[Fabricio Fachini]

Segui seu conselho e tentei utilizar SP!

Tenho certeza que estou fazendo algo errado, pois ainda não deu certo!

Vou tentar ser mais especifico para que você possa me ajudar.

Criei duas SP.

[PRIMEIRA SP]

CREATE PROCEDURE `func_atualiza_aplicaocontrole`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
     update aplicacontrole set aplicacontrole.controle = 1 where aplicacontrole.controle = 0;
END;

[sEGUNDA SP]

CREATE PROCEDURE `func_corrigeid`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE var_contador INT DEFAULT 1000;
    DECLARE var_idvago INT;
    DECLARE var_ultimoid INT;

    DECLARE cur_usuarios CURSOR FOR
      select identificadores from tmp_identificadores
      where identificadores not in (select id from usuarios)
      and identificadores < (select max(id) from usuarios);

    DECLARE cur_ultimoid CURSOR FOR
      select id from usuarios order by id desc;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;    

    OPEN cur_usuarios;
    OPEN cur_ultimoid;
    REPEAT
        FETCH cur_usuarios INTO var_idvago;
        IF NOT done THEN
          FETCH cur_ultimoid INTO var_ultimoid;
            if var_ultimoid <= var_idvago then
              set done = 1;
            else
              update usuarios set id = var_idvago where id = var_ultimoid;
            end if;
        END IF;
    UNTIL done END REPEAT;    
    CLOSE cur_usuarios;
    CLOSE cur_ultimoid;
END;

Na tabela usuarios, tenhos as seguintes definições:

CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL auto_increment,
  `cidade` char(40) NOT NULL default '',
  `nome` char(50) NOT NULL default '',
  `enderecoip` char(20) NOT NULL default '',
  `macaddress` char(20) default '',
  `idplano` int(11) NOT NULL,
  `velocidade` int(4) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `usu_uniq_ip` (`enderecoip`),
  KEY `usu_fk_plano` (`idplano`)
) ENGINE=MyISAM AUTO_INCREMENT=2484 DEFAULT CHARSET=latin1;

CREATE TRIGGER `usuarios_before_ins_tr` BEFORE INSERT ON `usuarios`
  FOR EACH ROW
BEGIN
     call func_atualiza_aplicaocontrole;
END;

CREATE TRIGGER `usuarios_before_upd_tr` BEFORE UPDATE ON `usuarios`
  FOR EACH ROW
BEGIN
     call func_atualiza_aplicaocontrole;
END;

CREATE TRIGGER `usuarios_before_del_tr` BEFORE DELETE ON `usuarios`
  FOR EACH ROW
BEGIN
     call func_atualiza_aplicaocontrole;
END;

Na tabela aplicacontrole, as definições são as seguintes:

CREATE TABLE `aplicacontrole` (
  `controle` tinyint(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TRIGGER `aplicacontrole_before_upd_tr` AFTER UPDATE ON `aplicacontrole`
  FOR EACH ROW
BEGIN
     call func_corrigeid;
END;

Veja, o conceito de trigger recursiva ainda esta sendo aplicado e, claro, o mesmo erro relatado anteriormente persiste!

Na pratica, em qualquer ação na tabela usuário, preciso verificar a tbl aplicacontrole, que tem apenas um registro, e se este conter o valor 1, devo atribuir o valor 0.

Espero que você possa me ajudar!

Obrigado

Editado Março 5, 2009 por Denis Courcy
Melhorar Entendimento

[Denis Courcy]

Oi, 'Fabricio Fachini'!

Observe este código na SP func_corrigeid

else
   update usuarios set id = var_idvago where id = var_ultimoid;
end if;

Veja a lógica:

cada vez que você chama uma alteração (inclusão/alteração/exclusão) em usuário a trigger é acionada e chama a SP func_atualiza_aplicaocontrole. Esta SP faz uma atualização em aplicacontrole e esta segunda tabela (aplicacontrole) chama a SP func_corrigeid que lê a tabela tmp_identificadores e procura por um "id vago". Ao encontrar o "id vago" ele promove uma atualização na tabela usuarios e então, começa tudo de novo. Indefinidamente.

Pense em uma solução que não crie a recursividade.