With your table structure description, it is clear that it does not have a primary key field whose values can be auto generated. MySQL's information_schema.tables does not hold auto_increment value but null for those fields which are not defined auto_increment.
Trigger issue:
The code block used in your trigger body seems depending on explicit calculation and input for the id fields. It did not use the default behaviour of an auto_increment field.
As per MySQL's documentation on LAST_INSERT_ID:
LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value
representing the first automatically generated value
successfully inserted for an AUTO_INCREMENT column
as a result of the most recently executed INSERT statement.
It is clear that it is for auto_increment fields only.
None of the fields id_1 and id_2 are attributed auto_increment.
Due to the reason, though you pass null as input for those fields while inserting, no value will be auto generated and assigned to them.
Alter your table to set auto_increment to one of those id_x fields, and then start inserting values. One caution is that passing a value explicitly to an auto_increment field during insertion will cause last_insert_id return a zero or most recent auto generated value, but not the NEW.id. Passing a null or not choosing the auto_increment field during insertion will trigger generation of NEW value for that field and last_insert_id can pick and return it.
Following example demonstrates above behaviour:
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)
Following statement shows next applicable auto_increment value for a 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 us try inserting a null value into the field.
mysql> insert into so_q27476005 values( null );
ERROR 1048 (23000): Column 'i' cannot be null
Above statement failed because input was into a not null primary key field but not attributed for auto_increment. Only for auto_increment fields, you can pass null inputs.
Now let us see the behaviour 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)
As the input was explicit and also the field is not attributed for auto_increment,
call for last_insert_id resulted a 0. Note that, this can also be some value else,
if there was another insert call for any other auto_increment field of another table,
in the same database connection session.
Let us see the records in the table.
mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Now, let us apply auto_increment to the field i.
mysql> alter table so_q27476005 change column i i int auto_increment;
Query OK, 1 row affected (0.66 sec)
Records: 1 Duplicates: 0 Warnings: 0
Following statement shows next applicable auto_increment value for the 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 last_insert_id still is the same.
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
Let us insert a null value into the field i.
mysql> insert into so_q27476005 values( null );
Query OK, 1 row affected (0.03 sec)
It succeeded though passing a null to a primary key field,
because the field is attributed for auto_increment.
Let us see which value was generated and inserted.
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
And the next applicable auto_increment value for the field i is:
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 us observe how last_insert_id results when explicit input is given 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 capture the value due to explicit input.
But, information schema do registered next 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 us observe how last_insert_id results when 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.
LAST_INSERT_ID()after the INSERT was completed, so after the trigger finished running. In other words, the trigger to generate the value for the second column of the two-column primary key, had no way to communicate the newly set value. I've added an example to the question. – Airglowshow create table tblouput to the post? – Kowtko