MySQL trigger + insert

Question

i'm trying to get my current id from a table pk to insert on another table fk, my next try is set a trigger inside database.

    CREATE PROCEDURE `INSERIR CODIGO DISPENSACAO` ()
    CREATE TRIGGER `productInsert`
        BEFORE INSERT ON `produtos_disp`
        FOR EACH ROW
    BEGIN
    set NEW.ID_PRODISP = (select max(ID)
                        from dispensacao p            
                       );
    END

what I want to set max id from dispensacao table which is going to be inserted from auto_increment on insert to it, on my fk codigo_disp for every row.

that will not realy work, as multiple connections could increase the id and you would have the wrong id. you should run the first, get the last_inserted_id, aned then the next in c# — nbk, Jul 04 '21 at 22:19
Use [`LAST_INSERT_ID()`](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id). Also don't you want `AFTER INSERT`? — Charlieface, Jul 04 '21 at 22:21
here is an example for last_insert_id https://stackoverflow.com/a/15057619/5193536 — nbk, Jul 04 '21 at 22:25
can you explain to me how it will get the last inserted id from a table that is not even mentioned on that example, how will it knows wich id to use — CANDINHO, Jul 04 '21 at 23:00
to clarify, what i doing is a delivery control app, so, someone call for a delivery(dispensacao table hold the info, who asked for(solicitante) who attended(deferido_por) date... but a person can ask for multiples itens and i need to insert on produtos_disp all products that belongs to its delivery, it's 1 delevery wich is 1 id to many products — CANDINHO, Jul 04 '21 at 23:06
CREATE PROCEDURE `INSERIR CODIGO DISPENSACAO` () CREATE TRIGGER `productInsert` BEFORE INSERT ON `produtos_disp` FOR EACH ROW BEGIN set NEW.ID_PRODISP = (select max(ID) from dispensacao p ); END something like that i guess — CANDINHO, Jul 04 '21 at 23:17

score 0 · Answer 1 · answered Jul 05 '21 at 01:23

Managed to get max id using a combobox as descending order. used

 MySqlConnection connection = new MySqlConnection("connectionString");

        string selectQuery = "select ID from dispensacao ORDER BY id DESC LIMIT 1";
        connection.Open();
        MySqlCommand command = new MySqlCommand(selectQuery, connection);
        MySqlDataReader reader = command.ExecuteReader();
        DataTable dt2 = new DataTable();
        dt2.Load(reader);           
        Cmbid.DisplayMember = "ID";            
       Cmbid.DataSource = dt2;

this return the max id from the table and all you need to do is make it invisible so user won't change as it is a combobox

MySQL trigger + insert

1 Answers1