mysql trigger to update one field of the newly added row

Question

I have 'users' table with the following fields:- user_id (int, auto increment PK) encrypted_userid (varchar 50) user_name (varchar 50) user_location (varchar 50)

What I want to do is create a trigger so that when values are inserted into the users table into user_name and user_location, i want to populate the encrypted_userid field with an AES_ENCRYPTED value from user_id - e.g. AES_ENCRYPT(user_id,'MYAESKEY') but only for the newly INSERTed row

Is this possible in MySQL with some kind of trigger?

Thanks in advance.

I need to try and do it this way, as the number of pages in the three websites that insert currently into the users table would take forever to change, so I thought using a trigger may be easier, as the encrypted_userid is going to be a new field, so I can update the new field against existing user_id values but for future entries once the new field goes live i need to do this — user2462433, Dec 12 '14 at 13:29

score 1 · Accepted Answer · answered Dec 14 '14 at 19:14

So is there a solution to my problem that will not fail etc? using a trigger - all other solutions i tried from reading other sources just didn't work.

Well all solutions revolve around LAST_INSERT_ID() because it's the only multi-user safe way to obtain auto generated ID.

First possible way, if you're very fond of triggers, is to have a separate table for auto generated sequences. Your schema will look like this

CREATE TABLE users_seq (user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE users
(
  user_id INT NOT NULL PRIMARY KEY DEFAULT 1, 
  encrypted_userid varchar(50), 
  user_name varchar(50), 
  user_location varchar(50),
  FOREIGN KEY user_id_fk (user_id) REFERENCES users_seq (user_id)
);

And the trigger

DELIMITER //
CREATE TRIGGER useridinserttrigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_seq() VALUES();
  SET NEW.user_id = LAST_INSERT_ID(),
      NEW.encrypted_userid = AES_ENCRYPT(LAST_INSERT_ID(), 'MYAESKEY');
END//
DELIMITER ;

Second way is to leverage your existing schema but use a stored procedure

DELIMITER //
CREATE PROCEDURE insert_user(IN _name VARCHAR(50), IN _location VARCHAR(50))
BEGIN
  DECLARE _id INT;

  START TRANSACTION;
  INSERT INTO users (user_name, user_location) VALUES(_name, _location);
  SET _id = LAST_INSERT_ID();
  UPDATE users 
     SET encrypted_userid = AES_ENCRYPT(_id, 'MYAESKEY')
   WHERE user_id = _id;
  COMMIT;
END//
DELIMITER ;

Sample usage:

CALL insert_user('johndoe', null);

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

I solved my problem using MySQL - Trigger for updating same table after insert (JCLG's entry)

CREATE TRIGGER `useridinserttrigger` BEFORE INSERT ON `users` 
FOR EACH ROW BEGIN
    DECLARE tmpid,tmpid2 INT(11);
    SELECT user_id INTO tmpid FROM users ORDER BY user_id DESC LIMIT 1;     
    SET tmpid2=tmpid+1;
    SET new.encrypted_userid=AES_ENCRYPT(tmpid2,'MYAESKEY'); 

END;

edited May 23 '17 at 11:50

Community

1
1

answered Dec 12 '14 at 14:12

user2462433

91
2
7

I hope you're aware that this solution will fail miserably under load. More over in your case it will fail silently by grabbing improper id and producing corrupted value. – peterm Dec 13 '14 at 00:36
So is there a solution to my problem that will not fail etc? using a trigger - all other solutions i tried from reading other sources just didn't work. – user2462433 Dec 14 '14 at 10:12

mysql trigger to update one field of the newly added row

2 Answers2