phpmyadmin Question posted in
The official documentation can be found here.

SQL error: 'Column [name] cannot be null' when trying to insert into table that has trigger set – Phpmyadmin

I have a trigger BEFORE INSERT that contains:

set new.order_id = concat(new.account_id, '.', lpad(new.id, 4, '0'))

Now when I try to insert some data with php like this:

$insertorder = '
INSERT INTO orders
(
  producten
)
VALUES
(
  "'.$conn->real_escape_string($encodedproductenarray).'"
)';

I get the following error: There was an error running the query [Column 'order_id' cannot be null]

So I tried inserting some data into the column order_id like this:

$insertorder = '
INSERT INTO orders
(
  order_id,
  producten
)
VALUES
(
  "1"
  "'.$conn->real_escape_string($encodedproductenarray).'"
)';
$insertordercon = $conn->query($insertorder);

But this gives the same error. Why? Do I need to change something in the trigger?

The trigger merges account_id and id (which is autoincrement) together with a dot inbetween and sets it to order_id.

When I insert empty data using phpmyadmin, the insert works and the trigger also does its job (except the value is 0.0000 since everything is empty) but for some reason when using below insert query I get the error.

Tags:

3

Answers


  1. 0

    You don’t have to write this part:

    (
  order_id,
  producten
)

    When you use the instruction INSERT you have to write all the values that every column should have, in order. If you want some "empty" column, just put the value null. Use this structure when making an insert.

    INSERT INTO table_name VALUES (value1,value2,value3)
    Login or Signup to reply.
  2. 0

    Please check the possible value of new.account_id. If it’s null then the result of concatenation will be null and you will get this error.

    Login or Signup to reply.
  3. 0

    The trigger merges account_id and id (which is autoincrement) together with a dot inbetween and sets it to order_id.

    But in a BEFORE trigger, the auto-increment has not yet generated a value. So id is NULL.

    The LPAD() function returns NULL if any argument is NULL.

    mysql> select lpad(NULL, 4, '0');
+--------------------+
| lpad(null, 4, '0') |
+--------------------+
| NULL               |
+--------------------+

    The CONCAT() function returns NULL if any argument is NULL.

    mysql> select concat('firstpart', '.', NULL);
+--------------------------------+
| concat('firstpart', '.', NULL) |
+--------------------------------+
| NULL                           |
+--------------------------------+

    The problem is that you can’t use the auto-increment number in a BEFORE trigger, and you can’t change NEW.order_id in an AFTER trigger.

    Therefore you can’t use a trigger to change any column based on the auto-increment value.

    You have to do this in a subsequent UPDATE after the INSERT. You can do the two statements in a transaction so no other session sees the row partially complete.

    START TRANSACTION;

  INSERT INTO orders ...;

  UPDATE orders SET order_id = CONCAT(account_id, '.', LPAD(id, 4, '0'))
  WHERE id = LAST_INSERT_ID();

COMMIT;

    P.S.: What happens after order #9999?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search