user7142440 user7142440 - 10 days ago 7
MySQL Question

ERROR 1136 on DELETE

I have this code and when I try the delete sentence on HeidiSQL it gives me this:
"ERROR 1136: Column count doesn't match value count at row 1"
I don't really understand why is this happening when I have another 2 trigger/delete that works perfect.
This is the code:

$triggersSQL="CREATE TRIGGER dev_cs BEFORE DELETE ON caja_sb FOR EACH ROW BEGIN
DECLARE cod_est varchar(11);
DECLARE cod_lej varchar(4);

SET cod_est = '$codE';
SET cod_lej = '$cod';

INSERT INTO caja_sorpresa VALUES (null, '$codCaja', '$anch', '$alt', '$prof', '$color', '$cont', NOW(), '$cod');

UPDATE estanteria
SET estanteria.lejas_ocupadas=estanteria.lejas_ocupadas+1
WHERE estanteria.codigo_estan=cod_est;

UPDATE leja
SET leja.ocupacion='1'
WHERE leja.cod_leja=cod_lej;

END";

$conexion->query($triggersSQL);

$ordenSQL="DELETE FROM caja_sb WHERE codigo_caja='$codCaja'";
$resultado=$conexion->query($ordenSQL);


the variables have the correct data.

Answer

The error says:

INSERT INTO caja_sorpresa VALUES (null, '$codCaja', '$anch', '$alt', '$prof', '$color', '$cont', NOW(), '$cod');

That means, the number of values you are writting in the VALUES() does not correspond to the value of fields in the table. If you want to insert data without specifying values for each column, than you would have to use a syntax like:

INSERT INTO tablename(column1, column2) VALUES(value1, value2);