MySQL – How to Fix Insert Row Issue After Creating a Trigger

MySQL

I get an error when inserting a row into a table after creating this trigger:

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF master_table_linfomastp2016 (IMHQ_solicitado = 'SI')
        THEN 
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;

The record im inserting is as follows:

INSERT INTO `master_table_linfomastp2016` (`ID_CASO`, `Tipo_de_estudio`, `Año`, `Codigo_interno`, `Iniciales_Px`, `Sexo`, `Edad`, `Servicio_solicitante`, `Roche_Hospital`, `Especimen`, `Macro`, `Diagnóstico`, `subtipo_ltp`, `FISH_solicitado`, `IMHQ_solicitado`, `laminilla_encontrada`, `laminilla_revisada`, `bloque_recibido`, `punch_realizado`, `en_microarreglo`, `validado_por`, `n_marcadores_solicitados`, `Localizacion`, `registro_HU`) VALUES (NULL, 'P', '15', '9106', 'IAS', 'M', '34', 'Hospital Universitario', NULL, 'BIOPSIA', 'REVISION DE LAMINIALLAS', 'LINFOMA NO HODGKIN T PERFIERICO NO ESPECIFICADO', 'Celulas T perifericas (Sin otra especificacion)', 'NO', 'SI', 'NO', 'NO', 'NO', 'NO', 'NO', 'JPF', '12', 'NODAL', '1383250-3')

and it says:

#1305 - FUNCTION linfomas_t_bd.master_table_linfomastp2016 does not exist

where did i go wrong? the insertion of records works if i remove the trigger, but I cannot insert records after creating the trigger and it gives me that error.

If i change the syntax from ` to ' it gives me syntax error. It also says sometimes that the table does not exist. What am i doing wrong? Im using phpMyAdmin but the trigger I made was using direct mysql input, not the trigger creator. The trigger is created without problems.

Best Answer

The IF statement should only check the column that is being inserted for 'SI', see revised code.

DELIMITER $$
CREATE TRIGGER aio_mtltp2016 AFTER INSERT ON master_table_linfomastp2016
BEGIN
        IF New.IMHQ_solicitado = 'SI'
        THEN 
        INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
        VALUES (NEW.ID_CASO, NEW.Tipo_de_estudio, NEW.Año, NEW.Codigo_Interno, NEW.Iniciales_Px, NEW.Sexo, NEW.Edad);
        END IF;
END;$$
DELIMITER ;

Related Solutions

Mysql – executing command prompt command in MySql trigger

system is one of MySQL's internal commands, interpreted by the mysql command-line tool rather than by the SQL engine, as stated in the manual:

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. [...]
system    (\!) Execute a system shell command.
[...]

You can't run the system command inside a stored program (and that includes a trigger), because the entire body of the stored program is sent to the server and the server won't recognise system.

However, you can get an output file with SELECT ... INTO OUTFILE FILE:

  SELECT field_1 INTO OUTFILE '/tmp/abc.txt'
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY '\n';

MySQL Trigger – How to Create Trigger for Update or Insert in Another Table

First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use AFTER UPDATE and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.