CODEWITHSUNDEEP
mysqltriggers
user3448267
user3448267

Reputation: 301

MySQL Triggers - Year-Month(YYMM) Prefix + Auto Increment ID

I saw another post explaining the use of triggers to create prefixed IDs
How to make MySQL table primary key auto increment with some prefix

http://sqlfiddle.com/#!2/0ed88/1

I am really new to triggers and I would like to find out if there's a way to have the prefix be the Year-Month (YYMM) instead of the preset 4 letters "LHPL" as written in the SQLfiddle?

Much appreciated! Lots to learn in this mysql journey!

    CREATE TABLE table1_seq
    (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    )|

    CREATE TABLE Table1
    (
      id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
    )|


    CREATE TRIGGER tg_table1_insert
    BEFORE INSERT ON table1
    FOR EACH ROW
    BEGIN
      INSERT INTO table1_seq VALUES (NULL);
      SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
    END |


    INSERT INTO Table1 (name) VALUES ('Jhon'), ('Mark')|

Upvotes: 1

Views: 2351

Answers (1)

spencer7593
spencer7593

Reputation: 108500

replace

'LHPL'

with

DATE_FORMAT(NOW(),'%y%m')

The NOW() and DATE_FORMAT() functions are documented here:

Reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 2

Related Questions