(Resolvido) Campo para identificar alteração em outro campo

[paulobergo]

Olá pessoal...

Já quebrei a cabeça para fazer algo que identifique alterações em determinados campos em uma tabela...

Explicando melhor, tenho uma tabela assim:

CREATE TABLE `teste`.`compra` (
  `idcompra` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `codigo` VARCHAR(14) NOT NULL DEFAULT 'codigoean?',
  `nome` VARCHAR(40) NOT NULL DEFAULT 'descriçao?',
  `unidade` VARCHAR(3) NOT NULL DEFAULT 'un?',
  `saldo` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `preço` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `pedir` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `flag` VARCHAR(4) NOT NULL DEFAULT '0000',
  PRIMARY KEY (`idteste`)
)
ENGINE = InnoDB;

Bom... o que eu preciso é de algo como um evento automático controlado pelo próprio MySQL que: a) quando crio um registro, automaticamente o campo `flag` é "setado" para '0000'; B) se eu alterar o codigo de um produto, o primeiro "bit" do campo flag passa a ser "1"... mas só se realmente o conteúdo for modificado... isto é... se o campo continha "78928201" e eu fizer um 'update `compra` set codigo="78928201';" sei que o proprio MySQL não efetuará a operação... c) se eu alterar o nome, o segundo "bit" passa para "1" e assim por diante... ou seja, se eu quiser saber se um campo, dentre estes quatro, sofreu alteração, basta olhar o campo flag... Pensei em algo assim, que seria executado pelo próprio MySQL antes ou após um update na tabela:

delimiter |
create trigger teste_upd after update on teste
  for each row begin
    if (old.codigo!=new.codigo) then
      update teste set flag=concat('1', substring(flag, 2, 3));
    end if;
  end;
|
delimiter;

Mas... como implementá-la? quando tento fazer um update (update teste set codigo="39393";), aparece uma mensagem de erro... can't update table 'teste' in stored funcion/trigger because it is already used by statement which invoked this stored function/trigger.

Grato por qualquer ajuda!!!

Abraços!

Editado Julho 23, 2010 por Denis Courcy

[Denis Courcy]

Oi, 'paulobergo'

Não deu certo porque o MySQL está se protejendo de uma recursividade (update aciona trigger que aciona update que aciona trigger...)

Tente assim:

delimiter |
create trigger teste_upd before update on teste
  for each row begin
    set NEW.flag = concat('1', substring(OLD.flag, 2, 3));
|
delimiter;

Fique tranquilo se houver rollback a gravação não será efetuada.

[paulobergo]

Claro! claro! é isso mesmo!!!!

Tinha que usar "set" mesmo...

Funcionou certíssimo...

Agora, ainda apareceu mais uma dúvida...

Tem jeito de criar uma variável em tempo de execução na query de update que possa ser "lida" pela trigger?

Tenho que usar duas queries no momento: uma para definir a variável e outra para a query propriamente dita...

Isso, já funciona:

Quero fazer um update em um campo na tabela e quero que a trigger atualize ou não outro campo.

A trigger é esta:

delimiter |
create trigger estoque_upd before update on estoque
  for each row begin
    if ((old.codigo!=new.codigo)or(old.nome!=new.nome))&&(@fix=1) then
      set new.flag=1;
      set new.modificado=now();
    end if;
  end;
|
delimiter;

Quando faço:

set @fix=0;

e em seguida:

update estoque 
set nome="hellmanns light", preço=2.40, codigo="7894000050737", compra="2010/07/19"
where codigo="7898905153289";

O produto tem seu nome e codigo alterados, mas o campo "flag" e o campo "modificado" permanecem intalterados. Mas, se fizer:

set @fix=1;

e em seguida:

update estoque 
set nome="hellmanns light", preço=2.40, codigo="7894000050737", compra="2010/07/19"
where codigo="7898905153289";

O produto tem seu nome e codigo alterados, e também o campo "flag" e o campo "modificado" atualizados. Daí, queria juntar as queries numa só... algo como:

update estoque 
set @fix=1, nome="hellmanns light", preço=2.40, codigo="7894000050737", compra="2010/07/19"
where codigo="7898905153289";

Mas dá erro... Consigo, fazendo isso, para definir a variável fix=1:

update estoque
set unidade = if(@fix := 1, unidade, unidade), nome="TIC TAC TUTTI-FRUTTI"
where codigo="7892024041011";

Ou para definir a variável fix=0:

update estoque
set unidade = if(@fix := 0, unidade, unidade), nome="TIC TAC TUTTI-FRUTTI"
where codigo="7892024041011";

Mas creio que haja um método mais "bonito"...

De qualquer forma, mais uma vez, grato por qualquer ajuda!

Abraços!

[Denis Courcy]

Oi, 'paulobergo'!

Creio que você possa usar uma temporary table e atualizar o campo FIX nela. Que tal?

Esta tabela poderá ser uma tabela de parametros e sua persistencia poderá ser volátil (engine = memory) ou não (engine = myisam).

[paulobergo]

Puxa... fico tão preocupado em otimizar as queries que me esqueço de outros recursos...

Realmente, uma tabela temporária é uma boa idéia... mas não para a situação específica, que a verificação de modificação de dados na tabela precisa ser feita --sempre--.

Também recebi uma sugestão de criar um campo varchar(32) para conter o MD5 com o "valor" (concatenado) de todos os outros campos que precisam ser monitorados... até sonhei que no MySQL eu pudesse fazer isso:

update estoque
set saldo=saldo+10,
data_ult_operacao=now(),
campomd5 = md5(saldo, data_ult_operacao)
where codigo="78920240";

Assim, caso alguém fizesse qualquer alteração nos campos saldo e data_ult_operacao, fora da aplicação (pelo Prompt, DBExplorer, PHPMyAdmin, etc...), eu conseguiria identificar, comparando o MD5 do campo "campomd5" com o MD5 do conteudo modificado...

E ainda vou tentar criar esta função, assim que tiver mais tempo...

Quanto à tabela temporária, vou sim implementá-la e ver se funciona melhor do que com a trigger...

De Qualquer forma,

Grato!

Abraços!