Trigger para zerar sequence ao mudar um determinado dado

[Kyosuke 0]

Pessoal, tenho a seguinte tabela no meu banco:

 

 

O meu problema é o seguinte, o campo anoExercício sempre receberá o ano atual, o que preciso é que, quando o valor adicionado ao anoExercício for diferente do ultimo valor adicionado, a sequence que incrementa o campo numero seja resetada.

 

Pessoal, não sei como fazer isso, acredito que isso seja resolvido com trigger, mas não estou conseguindo achar material bom sobre elas na internet e não sei fazer direito, então, se alguem puder me ajudar, nem que seja me indicando algum material, vou agradecer muito!

 

Abraços a todos!

[Motta 645]

Como PROCESSO.NUMERO é alimentado hoje ?

[-=Érico=- 5]

Kyosuke,

Se o campo numero não for AUTO_INCREMENT, a solução é trigger mesmo como você mesmo respondeu. :)

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON Processo
-> FOR EACH ROW
-> BEGIN
->     IF NEW.anoExercicio != OLD.anoExercicio THEN
->         SET NEW.numero = 1;
->     END IF;
-> END;//
mysql> delimiter ;

[Motta 645]

Para a solução do -=Érico=- funcionar, Processo deve ser tabela de controle, ou seja de um e apenas um registro.

[Kyosuke 0]

Bom @motta, Processo.numero é auto_increment, acho que talvez eu não tenha sido muito claro. O que preciso é que, quando for adicionado um novo anoExercicio, e este for diferente do ultimo ano cadastrado, o campo número volte a ser incrementado a partir do 0 novamente.

 

@-=Érico=-, acho que deve ser algo bem parecido com isto mesmo, porém, ao invés de fazer o campo número ser igual a 1, eu quero fazer com que o auto_increment passe a incrementar do 0 novamente.

Para a solução do -=Érico=- funcionar, Processo deve ser tabela de controle, ou seja de um e apenas um registro.

 

Não entendi, não saco muito bem de triggers.

[-=Érico=- 5]

Kyosuke,

 

Desculpe, achei que a coluna não fosse auto_increment e você estivesse atualizando um registro com um novo ano, se a coluna é auto_increment, basta criar uma chave primaria multipla sendo a chave secundaria o código numero.

CREATE TABLE processo (
numero int unsigned NOT NULL AUTO_INCREMENT,
anoExercicio smallint unsigned NOT NULL,
PRIMARY KEY (anoExercicio,numero)
);

Dessa maneira cada anoExercicio terá o seu AUTO_INCREMENT.

 

Chave primária multipla com AUTO_INCREMENT só funciona em tabelas MyISAM.

[Motta 645]

Não conheço MySql mas vai de forma automática iniciar NUMERO quando mudar ANO !?

[Kyosuke 0]

Então, dois problemas, primeiro que as tabelas são InnoDB e o segundo é que o campo anoExercicio não deve ser auto_increment só o campo número. o campo anoExercicio guarda o ano corrente. No domínio que estou trabalhando, os processos são identificados assim:

 

numero/anoExercicio

 

1/2014

2/2014

.

.

.

987654/2014

 

O que quero é que, quando mude o ano de exercício, o campo número volte a ser incrementado a partir do 0, sacou?

[-=Érico=- 5]

Motta,

Exatamente, para cada anoExericicio haverá um AUTO_INCREMENT, acabei de ver um exemplo no manual (CREATE table animals...)
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Mas pelo que parece não dá pra zerar esse auto_increment por coluna, eu tentei com um ALTER TABLE + WHERE e não funcionou, usar AUTO_INCREMENT dessa maneira é meio estranho mesmo.

Kyosuke,

O anoExercicio não será AUTO_INCREMENT, somente o campo numero, mas como você está usando InnoDB o que eu falei anteriormente não é mais relevante.

A única maneira de fazer o que você quer é você controlando o incremento, não utilizar AUTO_INCREMENT.

Pra pegar o próximo valor você teria fazer um SELECT. SELECT MAX(numero) + 1 WHERE anoProcesso = '2014' por exemplo.

Se não quer controlar isso na aplicação você pode colocar na trigger e atualizar a coluna com esse SELECT.

[Kyosuke 0]

Huumm, entendi. Então aquela primeira solução que você deu é válida né? Vou testar aqui!

[Motta 645]

Mas aquele "enum" deve ser definido na tabela, no caso do ano não precisaria.

 

Pelo visto funciona, vale um teste.

[-=Érico=- 5]

Mais ou menos, quando eu fiz a trigger eu achei que você estava atualizando um registro e alterando o anoExercicio. (Na verdade eu não sei porque eu achei isso. :assobiando: ).

Você teria que criar uma trigger pra INSERT em vez do UPDATE.

delimiter //


CREATE TRIGGER ins_check AFTER INSERT ON Processo
FOR EACH ROW
BEGIN

  DECLARE nextval INT;
  nextval = SELECT IFNULL(MAX(numero),0) + 1 WHERE anoExercicio = NEW.anoExercicio; 
  SET NEW.numero = nextval;

END//

delimiter ;

Alguma coisa assim, não testei a trigger.

[Kyosuke 0]

Bom, depois de muito penar (vejam a hora que estou postando rsrs) consegui chegar a isto:

CREATE TRIGGER ins_check BEFORE INSERT ON Processo
FOR EACH ROW
BEGIN
    DECLARE nextval INT;
    DECLARE lastAno INT;
    SET lastAno = (SELECT MAX(anoExercicio) FROM Processo);
    IF NEW.anoExercicio != lastAno THEN
        SET NEW.numero = 1;
    ELSE
        SET nextval = (SELECT IFNULL(MAX(numero),0) + 1 FROM Processo WHERE anoExercicio = NEW.anoExercicio); 
        SET NEW.numero = nextval;
    END IF;
END

Tá funfando direitinho. Vou só aguardar a opinião de vocês pra saber se tem algo a melhorar ai para dar como resolvido o tópico.

Abraços, e muito obrigado pela força!! :)

[Motta 645]

Creio ser uma boa solução, talvez fique lento se PROCESSO ficar com muitos registros a solução poderia ser indexer por Ano ou ter uma tabela de controle do ANO

[Kyosuke 0]

Será que isso vai afetar muito na performance do banco? Porque esta tabela irá receber um volume bem grande de dados. Se sim, como poderei resolver isso?

[Motta 645]

... a solução poderia ser indexar por Ano ou ter uma tabela de controle do ANO.

[-=Érico=- 5]

Muito bom!

Só não entendi porque você criou a primeira condição com o lastAno.

Quando for o primeiro processo daquele ano o comando SELECT IFNULL(MAX(numero),0) + 1 FROM Processo WHERE anoExercicio = NEW.anoExercicio vai retornar 1. Foi por isso que eu coloquei o IFNULL no comando, se for o primeiro processo vai fazer 0 + 1, que geralmente é 1. :D


Creio que indexar a coluna ano não iria ajudar muito na performance, já que não haverá muitos valores distintos nessa coluna.

[Kyosuke 0]

É verdade cara rsrsrs

 

Mas é tanta coisa na cabeça que já não estou nem raciocinando direito e como disse antes, não saco muito de triggers. Alterei aqui ficou de boa!

CREATE TRIGGER composite_auto_increment_Processo BEFORE INSERT ON Processo
FOR EACH ROW
BEGIN
    DECLARE nextval INT;
    SET nextval = (SELECT IFNULL(MAX(numero),0) + 1 FROM Processo WHERE anoExercicio = NEW.anoExercicio); 
    SET NEW.numero = nextval;
END

Agora, quanto a esse lance da performance, o impacto é muito grande? Porque o volume de dados nessa tabela vai ser alto.

[-=Érico=- 5]

Sem problema, eu entendo como é, rs. As vezes é bom parar um pouco e dar uma relaxada, fazer as coisas cansado acaba te dando mais problemas no futuro e ai você fica se perguntando porque diabos você escreveu aquilo, hehe.

Em relação a performance, vai ser questão de acompanhar a performance do banco.

O volume de dados vai ser alto quanto ? :)

[Motta 645]

De fato pensei num indice tipo bitmap , mas indice por ano não parece ser a melhor solução.