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

Ravinder Reddy · Accepted Answer · 2019-11-02T06:22:49.647

22

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.

edited Nov 02 '19 at 06:22

answered Jun 25 '14 at 09:37

Ravinder Reddy

23,692
6
52
82

I don't think this value is "transactional" meaning it may not be up to date by the time the trigger fires... – Lukas Eder Nov 27 '20 at 09:07
@LukasEder: please comment with your observations and suggest what alternative should be adopted – Ravinder Reddy Nov 27 '20 at 13:24
I already stated my observations, and I don't know what would be a better approach – Lukas Eder Nov 27 '20 at 14:02
@LukasEder: OK. Last statement in my answer too is a cautionary one. – Ravinder Reddy Nov 28 '20 at 15:18

score 10 · Answer 2 · answered Jun 25 '14 at 09:12

10

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; $$

answered Jun 25 '14 at 09:12

Abhik Chakraborty

44,654
6
52
63

How would I do it before, thou? – Keanu Correia Jun 25 '14 at 09:24
1

With before insert it does not make sense, once insert happens then only you will get the iduser from the user table. If you do with before insert then you need to select the last id from user table increment it by one and then insert on the table and thats not a proper way to do. Before insert is best for when you check incoming data and then change some values based on condition before inserting it. – Abhik Chakraborty Jun 25 '14 at 09:35
2

@AbhikChakraborty: No, it is not necessary for manual increment on the value. You can achieve this by reading next available auto_increment value to be assigned. Check my answer. – Ravinder Reddy Jun 25 '14 at 09:39
@RavinderReddy In a high load system and bulk inserting, will this be accurate? If I'm running a bulk insert (say an INSERT INTO ... SELECT FROM sort of thing), or if I'm running a high concurrency multi-threaded application for example. – Richard A Quadling Oct 30 '19 at 13:59
@RichardAQuadling: There seems several tests done on use of **auto_increment** capturing before next record is inserted. it is advised to not depend during batch processes. please read comments under one of my answers at https://stackoverflow.com/a/22343265/767881. – Ravinder Reddy Nov 01 '19 at 07:17
This is the better answer. – John Miller Dec 09 '19 at 06:49

score 2 · Answer 3 · answered Apr 16 '18 at 04:59

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.

savhascelik · Answer 4 · 2019-11-29T12:52:06.930

0

maybe this solution can

  BEGIN 
  DECLARE id int;
  SELECT MAX(table_id) 
         FROM table 
         INTO id;

     IF id IS NULL THEN 
        SET NEW.column=(CONCAT('KTG',1));
     ELSE
     SET NEW.column=(CONCAT('KTG',id+1));
    END IF;

   END

edited Nov 29 '19 at 12:52

answered Nov 29 '19 at 12:32

savhascelik

19
6

When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps, particularly for a question that already has an accepted answer. See: [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). – David Buck Nov 29 '19 at 12:53

Using Auto-Increment value in MYSQL Before Insert Trigger?

4 Answers4

Linked