title: Using Auto-Increment value in MYSQL Before Insert Trigger?
body: <p>The users table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The user_records table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The before insert trigger on the users table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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')?</p>
  
  <p>Also, if you see any other optimizations that could be made on the tables, feel free to let me know :D</p>