SQL trigger that outputs incremental numbers from zero whenever the month changes I have the following trigger that outputs a number in the format such as 170200005 where 17 is the year and 02 is the month (February) and changes as months changes e.g March 03 then the last digits are incremental.

BEGIN

  SET @var1 = LPAD((SELECT MONTH(CURRENT_DATE())), 2, '0');
  SET @var2 = (SELECT YEAR(CURRENT_DATE));
  SET @var2 = (SELECT MID(@var2, 3, 2));
  SET @var1 = CONCAT(@var2, @var1);

  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT(@var1, LPAD(LAST_INSERT_ID(), 5, '0'));

END


I would like to have a code that will output in the same format as highlighted before but then starts from 00001 whenever the month changes.
One way to do this would be to just maintain a table with the sequence you want to use, something like this:

Create the table:

mysql&gt; CREATE TABLE seq1 (seqnum int, primary key(seqnum));


Insert one time to have a row to update later:

mysql&gt; INSERT INTO seq1 VALUES (0);


Create a stored procedure to update the sequence number:

DELIMITER $
DROP PROCEDURE IF EXISTS setseq1;
CREATE PROCEDURE `setseq1`(OUT newseq INT)
-- Get a new sequence number that is the greater of the two-digit month number
-- plus a 5-digit sequence number, or the current sequence number + 1
BEGIN
DECLARE myseq INT;
DECLARE monthseq INT;
START TRANSACTION;
   SET myseq=(SELECT seqnum FROM seq1 FOR UPDATE);
   SET monthseq=(SELECT DATE_FORMAT(NOW(), '%y%m') * 100000 + 1);
   SET newseq = IF(myseq &lt; monthseq, monthseq, myseq + 1);
   UPDATE seq1 SET seqnum= newseq;
COMMIT;
END
$


Whenever you need a new sequence number, call the procedure, something like this:

mysql&gt; CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300016 |
+--------------+
1 row in set (0.00 sec)

mysql&gt; CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300017 |
+--------------+
1 row in set (0.00 sec)


The first time the procedure is called in any given month, it will set the sequence number to %y%m00001.  Thereafter it will be incremented by 1 each time the procedure is called until the month changes again.

Be sure not to mess with the sequence number table other than to insert one row initially and to call the procedure.