You will have to do three things:
Create a table to store the last contract number.
Create a trigger to ensure that each insertion is used in the sequence number
Create a function that calculates the next contract number based on the defined rules and the number found in the table quoted above.
No doubt you can not use AUTO INCREMENT of MySQL.
Something like this:
CREATE TABLE sequencia_contrato (
sequencial INT NOT NULL PRIMARY KEY,
ano INT
);
Trigger:
CREATE TRIGGER numero_contrato BEFORE INSERT ON contrato
FOR each ROW
BEGIN
SET NEW.contrato = PegaNumeroContrato();
END
Function:
Well, later I go back and try to put something in, but basically it will have to value the table above, add 10 if I understood that this is what you want. And catch the current year. If the current year is greater than the year recorded in the sequence control table, the year there should be updated for the current year and the sequence should be reset . Maybe that's not exactly what you want.
CREATE FUNCTION PegaNumeroContrato() RETURNS VARCHAR(10)
BEGIN
//precisa melhorar ainda, falta tratar o ano, por exemplo
DECLARE ultimo_valor INT;
SET ultimo_valor = (SELECT sequencial FROM sequencia_contrato);
SET ultimo_valor = ultimo_valor + 1;
UPDATE sequencia_contrato SET sequencial = ultimo_valor;
SET @resultado = (SELECT concat(lpad(ultimo_valor, 5, '0'), '-', CAST((SELECT ano FROM sequencia_contrato) AS CHAR(4))));
RETURN @resultado;
END
