CODEWITHSUNDEEP
mysql
William Hartanto
William Hartanto

Reputation: 13

MySQL - Auto Increment with Prefix as Primary Key on insert

MI_TEST_ID | MI_TESTNAME | MI_DATE_CREATED | MI_FLAG |

I've tried to make a trigger BEFORE INSERT but it's not working. Is it possible to make an auto increment with trigger before insert?

There's a built-in auto increment in MySQL but I want my test ID has a prefix like MI001 and so on.

  BEGIN 
INSERT INTO `mi_test` VALUES (NULL);
SET NEW.mi_test_id = CONCAT('MI', LPAD(LAST_INSERT_ID(), 3, '0'))

END

This is the trigger I used directly in the phpmyadmin trigger function. Whenever I try to insert. It always says column count does not match value count at row 1

I've looked solution in other thread, I found one of it requires another table for sequencing, but I need it on 1 query.

Upvotes: 0

Views: 1869

Answers (1)

Mahdi Aslami Khavari
Mahdi Aslami Khavari

Reputation: 1985

You can find current auto_increment value that is to be assigned to a new record. And use the same in the before trigger as a parent user id for user_records table. You have to query information_schema.tables table to find the value.

Example:

use `gknet`;

delimiter $$

drop trigger if exists before_create_user; $$

create definer=`root`@`localhost` trigger `before_create_user` 
       before insert on `users` 
for each row begin
  declare fk_parent_user_id int default 0;

  select auto_increment into fk_parent_user_id
    from information_schema.tables
   where table_name = 'users'
     and table_schema = database();

  insert into user_records ( action, userid, timestamp )
         values ( 'created', fk_parent_user_id, now() );
end;

$$

delimiter ;

Upvotes: 1

Related Questions