generating alphanumeric employees id using trigger in mysql?

Question

hello friends i want to create a 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 i want to generate unique employee code when i insert a new record in employees table in a sequence in employees table for example CG000001 CG000002... so on using mysql trigger but not able to do my logic is fail very time please help me

i m using this one--> 'code' create trigger mytrigger after insert on employees for each row set new.empcode := 'CG' + LPAD(convert(varchar, new.empcode), 6, '0'); — RAM CHOUBEY, Mar 06 '14 at 07:29
i m getting error "Updating of NEW row is not allowed in after trigger" — RAM CHOUBEY, Mar 06 '14 at 07:30
Syntactically, looks like you have missed the keyword UPDATE before SET. You can browse through these similar pages to find a solution for your problem. http://stackoverflow.com/questions/6400618/trigger-problem — Neels, Mar 06 '14 at 07:42

Ravinder Reddy · Answer 1 · 2014-03-12T06:09:42.173

Using after trigger you can't access to NEW as it already flushed.

You actually need a before trigger.

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

In case if you want to include newly generated auto_incremented value, then

create trigger bi_table_name before insert on employees
for each row begin
  set @auto_id := ( SELECT AUTO_INCREMENT 
                    FROM INFORMATION_SCHEMA.TABLES
                    WHERE TABLE_NAME='employees' AND TABLE_SCHEMA=DATABASE() ) 
  set new.empcode = concat( 'CG' + LPAD( @auto_id, 6, 0 ) );
end;

Note: Make sure that you don't have any pre-defined trigger with the same name and/or action. If have some, then drop them before creating the new.

generating alphanumeric employees id using trigger in mysql?

1 Answers1