SQL trigger that outputs incremental numbers from zero whenever the month changes

2 votes

2 answers

443 views

mysql

                          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.

Asked by amos chabwinja (21 rep)

Mar 3, 2017, 07:06 AM
Last activity: Aug 9, 2025, 09:03 AM

SQL trigger that outputs incremental numbers from zero whenever the month changes

Related Questions