Error: "Column Count doesn’t match value Count at Row 1" when inserting value in table with Trigger

Asked

Viewed 1,931 times

1

After creating the 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 table PRODUTO:

+-----------+-------------+------+-----+---------+----------------+
| 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 table BKP_PRODUTO:

+-----------+-------------+------+-----+---------+----------------+
| 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 cannot identify the error, since the inserted values correspond to those stipulated in both tables. Could someone help me identify the problem?

mysql sql-insert trigger
  • 1

    Young, you have in the Product table the IDPRODUCT field that does not accept NULL, however you are making an Insert with NULL

  • 1

    But in mysql, when inserting if we say the field is null it recognizes and auto-increments, but who knows it might be this

  • I tested your Insert here and it worked. Are you sure that are really these fields?

  • 1

    I believe it would be a good practice to add column names to Insert because if in the future you add more columns, your Insert gets messy.

  • 1

    @In Mysql it is possible to set a NULL value for an AUTO_INCREMENT, the auto increment value is automatically generated by the database. It may not be good practice, but it is possible and it works.

2 answers

2


You can’t set the null to the country IDPRODUTO because it cannot be null, as its structure shows, and also already is auto_increment, then it automatically generates the sequence by the internal counter.

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

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

Sometimes your bank doesn’t 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 Rigger, 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 pass only the VALUES without the fields, if sending all the fields it contains in the table, taking into account the characteristics and order.

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

A good option to learn, is to use Mysql Workbench, as all change, insertion, modification, etc., it shows all the code for you.

See more

  • After following all your tips, I still have the same mistake.

  • Douglas, you need to go by parts then. Try to do an Insert manually without Trigger. So far it’s working ?

  • I solved the problem, I had two triggers for the same PRODUCT table, one Rigger showed that such product was inserted ('I'), the other Rigger showed that such product was deleted (’D'), it turns out that I had made the Rigger of product deleted first and last the Rigger of Insert, changing the order of creation of the Riggers, ie by putting the Rigger of Insert first and the Delete last, it worked without problem! I don’t know if it really makes any difference, but that’s how I managed to solve the problem, can you tell me if it has any relation to the order of creation of the triggers?

  • There would be no problem. The Trigger (translating: trigger) is fired when there is an action in your table. So if it fits the condition, it will fire. But in addition, you have corrected your correct Inserts ? Because there is no way to let the null as you showed in the question.

  • That’s exactly what I thought, I see no logical relation in the creation order of triggers, and no! I didn’t "fix" my Inserts, I pointed out the NULL value inside the Riggers and it worked! I agree that it gets confused and may not be good practice, but it worked!

0

I was having the same problems, and only after the correction of Rigger de Insert, mentioning the variables inside that worked, and it was after Rigger delete, and I did vice and versa tbm, but it worked out well. Follows:

delimiter $

create trigger backup_produto
    after insert on PRODUTO
    for each ROW
BEGIN

    INSERT INTO BACKUP.BKP_PRODUTO (`IDPRODUTO`,`NOME`,`VALOR`) 
    VALUES (NEW.IDPRODUTO, NEW.NOME, NEW.VALOR);


END
$

delimiter ;

Browser other questions tagged mysql sql-insert trigger

You are not signed in. Login or sign up in order to post.