Q:

mysql trigger to update one field of the newly added row

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.

A:

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

