MySQL: Integrity error when inserting into database

Question

So, I have this one column in my table that gets filled by a trigger when a new entry is inserted.

CREATE TABLE `users` (
`idusers` int(11) NOT NULL AUTO_INCREMENT,
`uid` char(64) DEFAULT NULL,
`uname` varchar(80) NOT NULL,
`password` char(128) NOT NULL,
`mail` varchar(120) NOT NULL,
PRIMARY KEY (`idusers`),
UNIQUE KEY `uname_UNIQUE` (`uname`),
UNIQUE KEY `mail_UNIQUE` (`mail`),
UNIQUE KEY `uid_UNIQUE` (`uid`)
);
DELIMITER $$
TRIGGER `flask`.`users_BEFORE_INSERT` BEFORE INSERT ON `users` FOR EACH ROW
BEGIN
set new.uid = sha2(new.idusers, 256);
END$$
DELIMITER ;

The problem now is that when I try to add a new row (only have a test one yet because of the error), in the trigger the value of new.idusers is somehow always 0 instead of the current auto_increment value.
What do I need to change in my trigger code so that the value used for generating the uid is the actual id and not always 0?

Answer 1

Since idusers is an AUTO_INCREMENT field, its value is known only after the record is inserted in the table.
Use an AFTER INSERT trigger instead of a BEFORE INSERT trigger, and update the newly inserted record:

DELIMITER $$
CREATE TRIGGER `flask`.`users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW
BEGIN
UPDATE users SET uid = sha2(NEW.idusers, 256) WHERE idusers = NEW.idusers;
END $$
DELIMITER ;