Using Auto-Increment value in MYSQL Before Insert Trigger?

Question

The users table:

CREATE TABLE `users` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `username` varchar(16) DEFAULT NULL,
  `salt` varchar(16) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `loggedin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `sessionkey` varchar(60) DEFAULT NULL,
  `verifycode` varchar(16) DEFAULT NULL,
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `banned` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ip_address` varchar(45) DEFAULT NULL,
  `failedattempts` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `unlocktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

The user_records table:

CREATE TABLE `user_records` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(8) unsigned DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

The before insert trigger on the users table:

USE `gknet`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `before_create_user` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
INSERT INTO user_records (action, userid, timestamp)
  VALUES ('CREATED', ID, NOW() );
END

Basically, my problem here is that on the trigger when I try to put in the id of the user that's automatically assigned by MySQL (PK, NN, Auto-Increment), it just puts in 0 for userid on the user_records table. How would I do it so it would select the id that the user is being assigned by SQL, and put it in as userid on the records entry (where the ID is right after 'CREATED')?

Also, if you see any other optimizations that could be made on the tables, feel free to let me know :D

Answer 1

Change the trigger to after insert instead of before insert and use NEW to get the last inserted id

USE `gknet`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
TRIGGER `after_create_user` AFTER INSERT ON `users` 
FOR EACH ROW 
BEGIN
INSERT INTO user_records (action, userid, timestamp)
  VALUES ('CREATED', NEW.ID, NOW() );
END; $$

Answer 2

OP's comment:
How would I do it before, thou?

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 ;

Observations:
As per mysql documentation on last_insert_id(),

"if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only."

hence, depending on last_insert_id() and auto_increment field values in batch inserts seems not reliable.

Answer 3

PLEASE USE AFTER INSERT AND UPDATE

Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.