Why does not this trigger insert anything in any of the two tables?

Question

Why does not this trigger insert anything in any of the two tables?

Navigation

#1 by (0 votes)

0

The syntax does not mark any error which means that I have bad logic, the problem is that I am new in phpmyadmin and I do not know very well if I place the if correctly

DELIMITER $$
CREATE TRIGGER VALIDADOR BEFORE INSERT ON validaciones FOR EACH ROW BEGIN

    SET @FOLIO=(SELECT new.folio FROM validaciones);
    SET @NOMBRE= (SELECT new.nombre FROM validaciones);
    SET @APELLIDO=(SELECT new.apellido FROM validaciones);
    SET @COLONIA=(SELECT new.colonia FROM validaciones);
    SET @CALLE=(SELECT new.calle FROM validaciones);
    SET @NUM=(SELECT new.num FROM validaciones);
    SET @CP=(SELECT new.cp FROM validaciones);
    SET @CIUDAD=(SELECT new.ciudad FROM validaciones);
    SET @FORMA_DE_PAGO=(SELECT new.forma_pago FROM validaciones);
    SET @factura= (SELECT new.nombre_fact from validaciones);
    SET @TIPO= (SELECT new.type from validaciones);
    SET @DATOS=(SELECT new.data from validaciones);
    SET @usuario= (SELECT new.nombreusu from validaciones);
    SET @FECHA =(SELECT new.fecha FROM validaciones);

    SET @accion = "";

    IF @FOLIO = 0 THEN BEGIN SET @accion = "El error es el folio";
    END;
    END IF;
    IF @NOMBRE='E' THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @APELLIDO = 'E' THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @NUM = 0 THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @CP = 0 THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @CIUDAD = 'E' THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @FORMA_DE_PAGO ='E' THEN BEGIN SET @accion = "El error es el nombre";
    END;
    END IF;
    IF @accion <> "" THEN BEGIN 
    INSERT INTO log (fecha_val,error,nombre_fact,nombre) VALUES (@FECHA,@accion,@factura,@usuario);
    END;
    END IF;
    IF @accion = "" THEN BEGIN 
    SET @accion = "no hay error"; 
    INSERT INTO log (fecha_val,error,nombre_fact,nombre) VALUES (@FECHA,@accion,@factura,@usuario);
    INSERT INTO validaciones (folio, nombre,apellido,colonia,calle,num,cp,ciudad,forma_pago,nombre_fact,type,data,nombreusu,fecha) 
    VALUES (@FOLIO,@NOMBRE,@APELLIDO,@COLONIA,@CALLE,@NUM,@CP,@CIUDAD,@FORMA_DE_PAGO,@factura,@TIPO,@DATOS,@usuario,@FECHA);
    END;
    END IF;

END$$
DELIMITER ;

mysqli mariadb phpmyadmin

asked by Rafa 07.03.2018 в 20:16

source

1 answer

Error: ClientRect is not defined PHP JQUERY and PHP How to fill gridview asp.net c # sql from behindcode? [closed]

score 0 · Accepted Answer

Here are several problems, I will list them:

The selects at the beginning give an error, since they should be queries of 1 row and they return n rows, besides not needing data in the table.

Variables are not necessary since the data is in the new.

For code readability, those ifs can be reduced to a single line, without begin or end.

Twice the same question as @accion="" and @accion < > "", which can be solved with a single IF and its corresponding ELSE.

Last and most important of all, the trigger calls an insert in the table to which it belongs, which creates a recursive call resulting in an error.
The code with the respective applied corrections that I propose looks like this:

SET @accion = "";

IF new.folio = 0 THEN SET @accion = "El error es el folio"; END IF;
IF new.nombre ='E' THEN SET @accion = "El error es el nombre"; END IF;
IF new.apellido = 'E' THEN SET @accion = "El error es el nombre"; END IF;
IF new.num = 0 THEN SET @accion = "El error es el nombre"; END IF;
IF new.cp = 0 THEN SET @accion = "El error es el nombre"; END IF;
IF new.ciudad = 'E' THEN SET @accion = "El error es el nombre"; END IF;
IF new.forma_de_pago ='E' THEN SET @accion = "El error es el nombre"; END IF;

IF @accion = "" THEN
    SET @accion = "no hay error";
END IF;

INSERT INTO log (fecha_val,error,nombre_fact,nombre)
VALUES (new.fecha,@accion,new.nombre_fact,new.nombreusu);