Triggers - I need help solving the issue below

Question

I have the following situation :

The tables described above are ger_usuario, ger_mensagem and ger_destinos.

Being, the field proprietario (ger_mensagem) a FOREIGN KEY referencing the table ger_usuario.

Being, the field id_destino (ger_mensagem) a FOREIGN KEY referencing the table ger_destinos.

I need a Trigger to search the value of the last id_destino (ger_destinos) only then add it to the table ger_mensagem. In case the proprietario will be compared via code.

I made that Trigger :

DELIMITER $$
CREATE TRIGGER manut_destino BEFORE INSERT
ON ger_mensagem 
FOR EACH ROW

BEGIN

    DECLARE var_dest INT;


    INSERT INTO ger_destinos VALUES ('','',0);

    SELECT id_destino INTO var_dest FROM ger_destinos ORDER BY id_destino DESC; 

    INSERT INTO ger_mensagem VALUES ('','NEW.assunto','NEW.cat','NEW.rec','NEW.data_cria',
                                         'NEW.del','NEW.del_nom','NEW.del_data',1,var_dest);    

end $$

But it presents the following error :

Answer 1

Come on your Rigger has some errors, first take the dest value in the variable using the LIMIT 1, because if there is more than one record it will try to assign the variable and will give error. According to you can’t ALTERAR/INSERIR Records in the same table of Rigger if it will not enter in an infinite loop, think that whenever you enter will do an action and so consecutively, so let’s go there. to enter the record just you assign the value in this way SET NEW.id_destino = var_dest; and it will enter the value in the record you are entering, your Trigger will look like this then:

DELIMITER $$
CREATE TRIGGER manut_destino BEFORE INSERT
ON ger_mensagem 
FOR EACH ROW

BEGIN

    DECLARE var_dest INT;

    INSERT INTO ger_destinos VALUES (NULL,0,0);

    SELECT 
            id_destino INTO var_dest 
        FROM ger_destinos 
        ORDER BY id_destino DESC LIMIT 1; 

    SET NEW.id_destino = var_dest;

end $$