CODEWITHSUNDEEP
mysqltriggers
Marcelo Aymone
Marcelo Aymone

Reputation: 293

Auto Increment mysql trigger

How create a Auto increment field based on this example:

I have this table, with "AF" field, in that format: SN.MM.YYYY The "SN" = AI number based on last insert, MM= Atual Month, YYYY = Atual Year.

| ID |    AF       | 
____________________
|  1 | 01.10.2013  |
|  2 | 02.10.2013  |

So, when changes the month or year, the trigger must set "AF" field that way: Ex.: Month changes to November(Reset SN to 01). 

|  3 | 01.11.2013  |
|  4 | 02.11.2013  |

The same thing when year changes(Reset SN to 01):

|  5 | 01.01.2014  |
|  6 | 02.01.2014  |
|  7 | 03.01.2014  |

Anyone know's how set that trigger?

Obs: There may be more than one record in one day, so, day is not important.

Sorry for the bad english

Thanks guys!

Upvotes: 0

Views: 1660

Answers (1)

peterm
peterm

Reputation: 92785

Technically you can do something like this

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.af = CONCAT(
    LPAD(COALESCE(
      (SELECT MAX(LEFT(af, 2)) 
         FROM table1 
        WHERE af LIKE DATE_FORMAT(CURDATE(), '__.%m.%Y')), 0) + 1, 2, '0'), 
    DATE_FORMAT(CURDATE(), '.%m.%Y'));

Here is SQLFiddle demo

Note: This approach (creating your own ago_increment values with such a pattern) has two major drawbacks:

  1. Under heavy concurrent access different connections may obtain the same AF number
  2. Because of your particular AF pattern (SN comes first) using an index is impossible therefore you'll end up always getting a full scan

Upvotes: 1

Related Questions