Enter the identifier field generated by the trigger but not passed

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 receives a new identifier (from the object table), it inserts the identifier into the id column of the device table.

When I access it (e.g. from mysql_insert_id();in PHP), its empty.

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

Answer 1

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

Code for creating 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 the 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);

Hope I helped. :)

Answer 2

:

, LAST_INSERT_ID(), , .

, , , , .

• 1:

, objects, ( )

• 2:

:

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

• 3:

object;)

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

2 3 , , @myLastInsertID object .