0

The following trigger :

DELIMITER //
CREATE TRIGGER auo_mtltp  AFTER UPDATE ON master_table_linfomasltp2016
FOR EACH ROW
BEGIN
            IF (NEW.IMHQ_solicitado = 'SI')
            THEN 
            INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad,n_marcadores_solicitados)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad,OLD.n_marcadores_solicitados);
            END IF;

            IF (NEW.FISH_solicitado = 'SI')
            THEN
            INSERT INTO fish (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad);
            END IF;
    END//
DELIMITER ;

Adds a new row into a table if x condition is met. How ever, ID_CASO is a unique index into the child table, and like this, if i update a record already into the child table, it gives me an error, since there is another trigger running that adds the record to the child table if the x condition is met since insertion.

I need this trigger to run only IF the ID_CASO value is not present in the child table.

Any ideas?

I tried IF EXISTS syntax, but no succes so far.

Also, I thought that if you clarified the "NEW." prefix into the row identifier it would only fire the trigger if the value is NEW, but if the value is OLD and there is no NEW value being updated, it uses the OLD one and tries to add the row into the child table. As in, it just runs the trigger again, even if the value was OLD and not NEW.

3
  • Is this trigger "before" or "after an "insert" or what? – Rick James Sep 6 '16 at 20:43
  • This should help you out... stackoverflow.com/questions/5943650/if-not-exists-in-trigger – topshot Sep 6 '16 at 20:48
  • @RickJames thanks, I fixed it. Forgot to add that i just copied the phpmyadmin box for the actual code. And it is AFTER_UPDATE. topshot I just checked that post and im trying it. Not sure if it works yet. – Max Molina Sep 6 '16 at 20:52
0

Change the inserts to

INSERT IGNORE ...

That should avoid the problem as I read it. If I misread, please clarify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.