generating alphanumeric employees id using trigger in mysql? -

hello friends want create table employees

create table if not exists `employees` (   `sno` int(11) not null auto_increment,   `empcode` varchar(20) not null,   `employeename` varchar(20) not null,   `fathername` varchar(20) not null,   `gender` varchar(6) not null,   `dob` date not null,   `contactno` varchar(12) not null,   `address` varchar(100) not null,   `city` varchar(20) not null,   `state` varchar(20) not null,   `branch` varchar(20) not null,   `dateofjoining` date not null,   primary key (`sno`),   unique key `empcode` (`empcode`) ) engine=innodb default charset=latin1 auto_increment=1 ; 

and want generate unique employee code when insert new record in employees table in sequence in employees table example cg000001 cg000002... on using mysql trigger not able logic fail time please me

using after trigger can't access new flushed.

you need before trigger.

create trigger bi_table_name before insert on employees each row begin   set new.empcode = concat( 'cg' + lpad( new.empcode, 6, 0 ) ); end; 

in case if want include newly generated auto_incremented value,

create trigger bi_table_name before insert on employees each row begin   set @auto_id := ( select auto_increment                      information_schema.tables                     table_name='employees' , table_schema=database() )    set new.empcode = concat( 'cg' + lpad( @auto_id, 6, 0 ) ); end; 

note: make sure don't have pre-defined trigger same name and/or action. if have some, drop them before creating new.