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-02 06:22:49Z

23

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 2, 2019 at 6:22

answered Jun 25, 2014 at 9:37

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lukas Eder Over a year ago

I don't think this value is "transactional" meaning it may not be up to date by the time the trigger fires...

Ravinder Reddy Over a year ago

@LukasEder: please comment with your observations and suggest what alternative should be adopted

Lukas Eder Over a year ago

I already stated my observations, and I don't know what would be a better approach

Ravinder Reddy Over a year ago

@LukasEder: OK. Last statement in my answer too is a cautionary one.

Abhik Chakraborty · Accepted Answer · 2014-06-25 09:12:38Z

11

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, 2014 at 9:12

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

6 Comments

Abhik Chakraborty Over a year ago

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.

Ravinder Reddy Over a year ago

@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.

Richard A Quadling Over a year ago

@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.

Ravinder Reddy Over a year ago

@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 stackoverflow.com/a/22343265/767881.

John Miller Over a year ago

This is the better answer.

|

Abdul Aziz Al Basyir · Accepted Answer · 2018-04-16 04:59:51Z

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.

answered Apr 16, 2018 at 4:59

Abdul Aziz Al Basyir

1,25414 silver badges29 bronze badges

Comments

savhascelik · Accepted Answer · 2019-11-29 12:52:06Z

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, 2019 at 12:52

answered Nov 29, 2019 at 12:32

savhascelik

696 bronze badges

1 Comment

David Buck Over a year ago

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.

Collectives™ on Stack Overflow

Using Auto-Increment value in MYSQL Before Insert Trigger?

4 Answers 4

4 Comments

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related