Triggers - I need help solving the question below

Question

Triggers - I need help solving the question below

Navigation

#1 by (0 votes)

1

I have the following situation:

Thetablesdescribedabovearerespectivelyger_usuario,ger_mensagemandger_destinos.

Thefieldproprietario(ger_mensagem)isaFOREIGNKEYreferringtotheger_usuariotable.

Thefieldid_destino(ger_mensagem)isaFOREIGNKEYreferringtotheger_destinostable.

IneedaTriggerthatfetchesthevalueofthelastid_destino(ger_destinos)toonlythenaddittotheger_mensagemtable.Inthiscasetheproprietariowillbecomparedviacode.

ImadethisTrigger:

DELIMITER$$CREATETRIGGERmanut_destinoBEFOREINSERTONger_mensagemFOREACHROWBEGINDECLAREvar_destINT;INSERTINTOger_destinosVALUES('','',0);SELECTid_destinoINTOvar_destFROMger_destinosORDERBYid_destinoDESC;INSERTINTOger_mensagemVALUES('','NEW.assunto','NEW.cat','NEW.rec','NEW.data_cria','NEW.del','NEW.del_nom','NEW.del_data',1,var_dest);end$$

Butithasthefollowingerror:

mysql trigger

asked by anonymous 02.12.2017 / 04:16

1 answer

I can not instantiate my class to db Listening / Recording speechRecognition audio

score 0 · Answer 1

Let your trigger have some errors, first, get the value of dest in the variable using LIMIT 1 , because if there is more than one record it will try to assign the variable and it will give error. Second you can not ALTERAR/INSERIR Records on the same trigger table if not it will enter into an infinite loop repeat, think that whenever you enter it will do an action and so on, so come on. to insert the record you only have to assign the value SET NEW.id_destino = var_dest; and it will insert the value in the record you are inserting, your trigger will look like this:

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 $$