Scriptcaser

Gerando número de protocolo com triggers no MySQL

Olá caser, hoje não ensinarei nenhuma dica sobre scriptcase, mas uma dica não menos importante. No processo de diagramação entidade-relacional de um banco de dados, existem recursos que otimizam o seu código, fazendo com que você não precise criar um algoritmo para resolver um problema a nível de aplicação.

Um cliente me passou uma demanda, ele queria que fosse incorporado ao seu ERP um algoritmo de geração de protocolo de atendimento. O fluxo é simples: o pessoa vai até a secretaria da empresa para resolver alguma questão, o seu atendimento é registrado através de um formulário e ao validar as informações é gerado um número de protocolo que pode ser enviado para uma impressora térmica.

O layout de protocolo segue um padrão muito comum amplamente utilizado, YYYY (ano corrente) + sequencial de número máximo 9999, sendo que ao virar o ano o contador sequencial deve reiniciado para 1.

Escolhi um número máximo do sequencial pois na realidade do meu cliente não haverá mais do que 9999 protocolos gerados por ano, também por quê depois do ano e antes do sequencial completarei com zeros à esquerda. Ex: 20190001, 20190002 … 20190123.

Como disse, resolver esse problema a nível da aplicação é fácil, o problema é que serão feitas consultas ao banco de dados e isso afetará o desempenho do sistema. É aí que entra a trigger.

Nos Sistemas de Gerenciamento de Banco de Dados (SGBD) SQL, a trigger é um evento que executa um algoritmo SQL, podendo ele ser antes ou depois de um insert, update ou delete.

No nosso caso, o protocolo deve ser gerado antes do insert, logo criaremos a trigger no evento before insert.

Vejamos a sintaxe de uma trigger:

CREATE TRIGGER nome momento evento
ON tabela
FOR EACH ROW
BEGIN
/*corpo do código*/
END

Sendo assim, a nossa trigger de geração de protocolo ficará assim:

CREATE TRIGGER `add_protocol` BEFORE INSERT ON `atendimento_secretaria` FOR EACH ROW BEGIN

DECLARE prev_protocolo INT;

SELECT 
    protocolo
INTO prev_protocolo FROM
    atendimento_secretaria
ORDER BY id DESC
LIMIT 1;

IF prev_protocolo IS NULL THEN
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(1, 4, '0'));
ELSEIF SUBSTR(prev_protocolo, 1, 4) <> DATE_FORMAT(CURRENT_TIMESTAMP, '%Y') THEN
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(1, 4, '0'));
ELSE
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(TRIM(LEADING 0 FROM SUBSTR(prev_protocolo, 5, 4)) + 1, 4, '0'));
END IF;
END

O que significa tudo isso? Calma que eu vou explicar parte por parte o que acontece depois do BEGIN, vamos la!

A primeira coisa a se fazer é declarar uma variável que vai armazenar o último protocolo gerado, nomeei de prev_protocolo.

DECLARE prev_protocolo INT;

Feito isso, agora fazemos uma consulta SQL para pegar o último protocolo gerado e armazenar na nossa variável criada anteriormente através do SELECT INTO.

SELECT 
    protocolo
INTO prev_protocolo FROM
    atendimento_secretaria
ORDER BY id DESC
LIMIT 1;

Note que o campo protocolo não é a minha chave primária mas o campo id, fiz isso para evitar problemas de restrição de chave.

Por fim onde a mágica acontece, utilizaremos uma estrutura condicional bem conhecida dos programadores, o IF ELSE.

IF prev_protocolo IS NULL THEN
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(1, 4, '0'));
ELSEIF SUBSTR(prev_protocolo, 1, 4) <> DATE_FORMAT(CURRENT_TIMESTAMP, '%Y') THEN
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(1, 4, '0'));
ELSE
	SET NEW.protocolo = CONCAT(DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y'), LPAD(TRIM(LEADING 0 FROM SUBSTR(prev_protocolo, 5, 4)) + 1, 4, '0'));
END IF;

IF: Se prev_protocolo for NULL, ou seja, se naquele momento for o primeiro número de protocolo gerado, então comece pelo número 1.

ELSEIF: Se o ano de prev_protocolo for diferente do ano atual, reinicie o contador para 1 e adicione o ano atual na frente.

ELSE: Se nenhuma das condições anteriores forem atendidas, pegue o último sequencial da número, sanitize (limpe os zeros à esquerda) e some mais 1.

Veja a função LPAD, é com ela que especifico quantas casas sejam completadas com zeros à esquerda do meu sequencial.

Espero que você tenha gostado, não esqueça de deixar o seu comentário e se tiver alguma dúvida pode postar que eu respondo.

scriptcaser

9 comments

  • Também gostaria de ver um demo. \o/

    Aproveitando, seria muito bom se a turma do Scriptcase implementasse isso nativamente, de forma que pudessemos aproveitar os CRUD’s visuais e os eventos AfterPost, BeforePost, etc.

    • Maicon, no evento onAfterInsert você pode chamar a variável que ela já virá populada. Aí poderia usar combinado com o sweetlaerts para que no reload do form o protocolo aparecer como um popup, que é assim que eu uso.

  • Olá,

    Meu nome é Abner e estou com problemas para criar um trigger no meu banco mysql. Eu segui os passos mas não consegui criar. Segue os dados do meu projeto:

    – Tenho um banco de dados chamado (dbdenuncia);
    – Tenho uma tabela chamada (usuarios) com uma chave primária com AUTO INCREMENT chamada (ID);
    – Criei uma tabela adicional com uma chave primária chamado (cod_protocol);

    Preciso criar um trigger igual o da sua postagem, que gere um número de protocolo.

    Poderia me ajudar?

    • Na tabela adicional onde existe a chave primária cod_protocol, ela não pode ser auto-increment. A trigger é acionada no before insert insert e popula o registro anted da inserção, mas caso você deseje usar uma tabela para popular usaria no after insert, pois uma vez validade o registro poderia gerar o protocolo.

      Se puder me mandar um esquema visual do banco e dos relacionamentos ficaria melhor para responder a sua dúvida.

  • Hola, muy buen ejemplo y gracias por compartirlo, me funciona perfecto el código. Lo que me gustaría preguntarte es ¿cómo puedo hacer para se muestre el número de protocolo en el formulario, antes de presionar el botón de guardar?

    • En este caso, no es posible mostrar el protocolo antes de guardar el registro. Lo que puede hacer, junto con sweetalerts, en el evento onAfterInsert es abrir el protocolo después de guardar.

Newsletter

Se inscreva na nossa newsletter
Se increva hoje na nossa lista de e-mail para receber atualizações, tutoriais e ofertas especiais!

Respeitarei sua privacidade. Seu e-mail nunca será compartilhado.