Insert ID field generated from trigger, but not passed along

Question

In MySQL, I have a trigger:

BEGIN
   IF (EXISTS(SELECT * FROM devices WHERE device_id = NEW.device_id)) THEN
    SET NEW.id = NULL;
   ELSE
INSERT INTO objects (object_type) VALUES ('3');
SET NEW.id = LAST_INSERT_ID();
   END IF;
END

When this trigger gets a new id (from the objects table) it inserts the id into the id column of the devices table.

When I refer to it (for example with mysql_insert_id(); in PHP), its empty.

How can I return the insert id from the trigger (LAST_INSERT_ID();) to the function in PHP as the mysql_insert_id(); ?

`SELECT LAST_INSERT_ID();` should work if `mysql_insert_id();` cannot obtain it, however if the ID you want is from a insertion prior the trigger then I am afraid you're out of luck. — Prix, Mar 07 '14 at 04:24

score 3 · Answer 1 · answered Mar 12 '14 at 14:11

Personally I use stored procedures.
Here is a basic example with PDO:

Code to create the Stored Procedures:

CREATE DEFINER=`user`@`localhost` PROCEDURE `InsertUser`(IN `Input_username` INT, OUT `Out_ID` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

   INSERT INTO users(
        username)
    VALUES (
    Input_username);

    SET Out_ID = LAST_INSERT_ID();
    SELECT Out_ID;
END

And PHP code:

  $insert = "CALL InsertUser(:Input_username,
                             @Out_ID)";
  $bdd = new PDO('mysql:host=localhost;dbname=db-name', 'user', 'password');

  $stmt = $bdd->prepare($insert);     
  $stmt->bindParam(':Input_username', rand(), PDO::PARAM_STR); // to create random name

  $stmt->execute();
  $tabResultat = $stmt->fetch();
  $id_user = $tabResultat['Out_ID'];
  var_dump($id_user);

I hope I have helped. :)

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

This behaviour is by design:

If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value is seen by statements that follow the procedure call.

For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.

Workaround 1: Stored Procedures

Unfortunately this introduces a risk of inconsistencies between your table and objects, as insertions could still happen outside of this procedure (this problem could be adressed with convoluted access restrictions on the table)

Workaround 2:

Save the value in a user variable:

CREATE TRIGGER
....
BEGIN
    INSERT INTO objects (object_type) VALUES ('3');
    SET NEW.id = LAST_INSERT_ID();
    SET @myLastInsertID = LAST_INSERT_ID();
END //

INSERT INTO your_table... -- trigger the above
SELECT @myLastInsertID; -- here is your value

Workaround 3:

Simply get the value from object ;)

INSERT INTO your_table... -- trigger the above
SELECT MAX(autoinc_column) FROM objects; -- here is your value!

Workarounds 2 and 3 should be wrapped in a transaction to ensure no-one interferes with @myLastInsertID or object during the process.

Insert ID field generated from trigger, but not passed along

2 Answers2

Linked