Error: "Column count does not match value count at row 1" when inserting value in table with trigger

Navigation
1

After creating trigger : 

DELIMITER #

CREATE TRIGGER BACKUP_PRODUTO_INS
AFTER INSERT ON PRODUTO
FOR EACH ROW
BEGIN
    INSERT INTO BACKUP.BKP_PRODUTO VALUES(NULL, NEW.IDPRODUTO, NEW.NOME, NEW.VALOR, 'I');
END
#

DELIMITER ;

DESC of the PRODUTO table: 

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| IDPRODUTO | int(11)     | NO   | PRI | NULL    | auto_increment |
| NOME      | varchar(30) | YES  |     | NULL    |                |
| VALOR     | float(10,2) | YES  |     | NULLL   |                |
+-----------+-------------+------+-----+---------+----------------+

DESC of the BKP_PRODUTO table: 

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| IDBKP     | int(11)     | NO   | PRI | NULL    | auto_increment |
| IDPRODUTO | int(11)     | YES  |     | NULL    |                |
| NOME      | varchar(30) | YES  |     | NULL    |                |
| VALOR     | float(10,2) | YES  |     | NULL    |                |
| EVENTO    | char(1)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

When executing the following statement : 

INSERT INTO PRODUTO VALUES(NULL, "LIVRO TESTE", 100.00);

The following error is generated in MySQL 5.7: 

Column count doesn't match value count at row 1

I can not identify the error, since the values entered correspond to those stipulated in both tables. Could someone help me identify the problem?

    
asked by anonymous 15.03.2018 / 04:13

1 answer

2

You can not set the null to the IDPRODUTO field because it can not be null, as its structure shows, and is already auto_increment , so it automatically generates the sequence by the internal counter.

The correct one is to set only the fields that will pass the values: 

INSERT INTO PRODUTO ('NOME','VALOR') VALUES ("LIVRO TESTE", 100.00);

Sometimes your bank does not force the "'", so you can take: 

INSERT INTO PRODUTO (NOME,VALOR) VALUES ("LIVRO TESTE", 100.00);

To add several at once: 

INSERT INTO PRODUTO ('NOME','VALOR') VALUES ("LIVRO TESTE 1", 100.00), ("LIVRO TESTE 2", 200.00), ("LIVRO TESTE 3", 300.00);

For your trigger, just hit your INSERT by bringing the necessary information, basically: 

INSERT INTO BACKUP.BKP_PRODUTO ('IDPRODUTO','NOME','VALOR','EVENTO') VALUES (NEW.IDPRODUTO, NEW.NOME, NEW.VALOR, 'I');

Complementing:

You can only pass the VALUES without the fields, if you will send all the fields that it contains in the table, taking care of the characteristics and order. 

INSERT INTO PRODUTO VALUES (10, "LIVRO TESTE", 100.00);
  

A good option to learn is to use the Mysql Workbench because   change, insert, modify, etc, it shows all the code for   You.

See more

    
15.03.2018 / 13:42
How do I make an alert near the return date? Share html android file