MySQL Forums
Forum List  »  Triggers

Trigger AFTER_UPDATE to only run IF NOT EXISTS
Posted by: Max Molina
Date: September 06, 2016 01:54PM

The following trigger :

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


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

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger AFTER_UPDATE to only run IF NOT EXISTS
3272
September 06, 2016 01:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.