In his description of the table structure, it is clear that there is no primary key field in it, the values of which can be generated automatically. MySQL information_schema.tables does not have auto_increment , but null for fields that are not defined by auto_increment .
Trigger Problem :
The code block used in your trigger enclosure seems to depend on the explicit calculation and input for the id fields. He did not use the default behavior for the auto_increment field.
According to MySQL Documentation by LAST_INSERT_ID :
LAST_INSERT_ID () returns BIGINT UNSIGNED (64-bit)
representing the first auto-generated value
inserted successfully for AUTO_INCREMENT column
as a result of the last executed INSERT statement.
It is clear that this is only for the auto_increment fields.
None of the fields id_1 and id_2 are assigned to auto_increment .
Due to the reason, although you pass null as input for these fields when pasting, no value will be automatically generated and assigned to them.
Modify the table to set auto_increment to one of these id_x fields, and then start pasting the values. One caveat is that passing the value explicitly to the auto_increment field during insertion will cause last_insert_id return zero or the most recent automatically generated value, but not NEW.id Passing a null or not selecting the auto_increment field during insertion will generate a NEW value for this field, and last_insert_id can select and return it.
The following example demonstrates the behavior above :
mysql> drop table if exists so_q27476005; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table so_q27476005( i int primary key ); Query OK, 0 rows affected (0.33 sec)
The following statement shows the next applicable auto_increment value for the field.
mysql> select auto_increment -> from information_schema.tables -> where table_name='so_q27476005'; +----------------+ | auto_increment | +----------------+ | NULL | +----------------+ 1 row in set (0.00 sec)
Let's try to insert a null value in the field.
mysql> insert into so_q27476005 values( null ); ERROR 1048 (23000): Column 'i' cannot be null
The error above is because the input was in the not null primary key field, but not related to auto_increment . Only for auto_increment fields can you pass null inputs.
Now let's look at the behavior of last_insert_id :
mysql> insert into so_q27476005 values( 1 ); Query OK, 1 row affected (0.04 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
Since the input was explicit, and also the field was not assigned to auto_increment ,
a call to last_insert_id resulted in a 0 . Note that this may also be some else value if there was another insert call for any other auto_increment field of another table, in the same database connection session.
Let's see the entries in the table.
mysql> select * from so_q27476005; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec)
Now apply auto_increment to field i .
mysql> alter table so_q27476005 change column ii int auto_increment; Query OK, 1 row affected (0.66 sec) Records: 1 Duplicates: 0 Warnings: 0
The following statement shows the following applicable value of auto_increment for field i .
mysql> select auto_increment -> from information_schema.tables -> where table_name='so_q27476005'; +----------------+ | auto_increment | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec)
You can cross-check that the parameter last_insert_id still the same.
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec)
Insert the null value in the i field.
mysql> insert into so_q27476005 values( null ); Query OK, 1 row affected (0.03 sec)
This was possible by passing the null to a primary key field because the field is assigned to auto_increment .
Let's see what value was generated and inserted.
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
And the following applicable auto_increment value for field i :
mysql> select auto_increment -> from information_schema.tables -> where table_name='so_q27476005'; +----------------+ | auto_increment | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) mysql> select * from so_q27476005; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)
Now let's see how last_insert_id obtained when an explicit input is specified for the field.
mysql> insert into so_q27476005 values( 3 ); Query OK, 1 row affected (0.07 sec) mysql> select * from so_q27476005; +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
You can see that last_insert_id did not commit the value due to explicit input.
But, the information scheme registered the following applicable value.
mysql> select auto_increment -> from information_schema.tables -> where table_name='so_q27476005'; +----------------+ | auto_increment | +----------------+ | 4 | +----------------+ 1 row in set (0.08 sec)
Now let's see how last_insert_id obtained when the input for the field is auto / implicit.
mysql> insert into so_q27476005 values( null ); Query OK, 1 row affected (0.10 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 4 | +------------------+ 1 row in set (0.00 sec)
Hope these details help you.