0

I am trying to create in MySQL a 'custom' unique ID based on existing primary key (auto-incremented) 'id' field

delimiter //
CREATE TRIGGER update_id AFTER INSERT ON test 
BEGIN
    UPDATE test SET PN="PN-"+NEW.id;
END;
//
delimiter ;

I am using PhpMyAdmin and I really don't know if it's a UI problem (I encountered some problems in the past while creating triggers in PhpMyAdmin) or I really do something wrong.

What I need is a custom PN field that is automatically updated when insert new records in table, based on some text prefix "PN-"

id    PN    other fields
-------------------------
...
...
...
1253  PN-1253
1254  PN-1254

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN
        UPDATE test SET PN="PN-"+NEW.id;
    END' at line 2

I read in some other StackOverflow post that this will be impossible (to update) on the same table AFTER insert.

There is a solution with this? Thanks.

user1797147
  • 915
  • 3
  • 14
  • 33

1 Answers1

1

Get the new value and add it before insert 

   delimiter //
    CREATE TRIGGER update_id BEFORE INSERT ON test 
    BEGIN
        SET @pn:= ( SELECT AUTO_INCREMENT 
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_NAME='test'
                          AND TABLE_SCHEMA=DATABASE() ); 
      SET new.PN= CONCAT('PN-',@pn);
    END;
    //
    delimiter ;

Here

Mihai
  • 26,325
  • 7
  • 66
  • 81
  • May be a silly question, this is safe for multi user that asynchronously may obtain the ID from schema, then insert? – user1797147 Jun 03 '17 at 08:30
  • @user1797147 What is async in your case?This will work for multi users,exactly what the db was designed for, – Mihai Jun 03 '17 at 08:42