Trigger is not working.

[sandeep nandha]

Hi,
I have created a mysql table called users to have the insert into the table when the users sign up from php script.

CREATE TABLE IF NOT EXISTS `users` (
`userid` INT NOT NULL auto_increment,
`emp_id` VARCHAR(10) NULL,
`name` VARCHAR(50) NOT NULL,
`username` VARCHAR(40) NOT NULL,
`email` VARCHAR(45) NOT NULL,
`password` VARCHAR(40) NOT NULL,
PRIMARY KEY (`userid`),
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
UNIQUE INDEX `emp_id_UNIQUE` (`emp_id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 1001;


I want the employee id to be concatenated with a string and user id . For this, I created a trigger to generate the employee id before insert. But, the trigger is not working when I try to insert. Kindly advise.


DELIMITER |
DROP TRIGGER if exists users_insert_trg |
CREATE TRIGGER users_insert_trg BEFORE INSERT ON users
FOR EACH ROW BEGIN
DECLARE next_id INT;
SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users');
SET new.emp_id = CONCAT('E' , RIGHT(CONCAT('000',next_id),6)) ;
END;
|
DELIMITER ;



mysql> insert into users (name,username,email,password) values('test','[e-mail]','testuser','sdfd');

ERROR 1048 (23000): Column 'emp_id' cannot be null