MySQL - Trigger - Before Insert and using the SK (Auto Increment)

I have a simple posts table in MySQL which has a POST_ID as the SK (surrogate key). Replies to the original post ID are stored in the same table in a PARENT_POST_ID column, but I want to perform the following logic:

BEFORE INSERT (I think ...)

IF a PARENT_POST_ID has not been defined on the INSERT, then default the row value to the newly generated POST_ID (from the auto-int sequence)

IF a PARENT_POST_ID has been defined on the INSERT, then set it to whatever has been passed.

Example

post_id | parent_post_id | date_time        | message
     12               12   2015-04-14 21:10   A new post (start of a thread)
     13               12   2015-04-14 21:12   A reply to the post ID 12

The answer here: https://stackoverflow.com/a/11061766/1266457 looks like it might be what I need to do, although I am not sure what it's doing.

Thanks.

Solution

For before insert trigger you can not get the last inserted primary key , the other way of doing it is to get the max value from the table and increment it.

Here is a way to do it

delimiter //
create trigger posts_before_ins before insert on posts
for each row 
begin
  declare last_id int; 
  if new.parent_post_id is null then
    select max(post_id) into last_id from posts ;
    if last_id is null then
      set new.parent_post_id = 1 ;
    else
      set new.parent_post_id = last_id+1 ;
    end if ;
   end if ;
end ;//

delimiter ;

So the trigger will check if there is no value of parent_post_id in the insert query it will get the max post_id. For the first entry it will be null so we are setting it as 1 i.e. and after that max post_id + 1 after each entry.

Here is a test case of this in mysql

mysql> select * from test ;
Empty set (0.00 sec)

mysql> delimiter //
mysql> create trigger test_is before insert on test
    -> for each row 
    -> begin
    ->   declare last_id int; 
    ->   if new.parent_id is null then
    ->     SELECT auto_increment into last_id
    ->     FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'test'
    ->     and TABLE_SCHEMA = 'test';
    ->     set new.parent_id = last_id ;
    ->    end if ;
    -> end ;//
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> delimiter ;

mysql> insert into test (val) values ('aa');
Query OK, 1 row affected (0.10 sec)

mysql> insert into test (val) values ('bb');
Query OK, 1 row affected (0.04 sec)

mysql> select * from test ;
+---------+-----------+------+
| post_id | parent_id | val  |
+---------+-----------+------+
|       1 |         1 | aa   |
|       2 |         2 | bb   |
+---------+-----------+------+
2 rows in set (0.00 sec)