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

i have simple posts table in mysql has post_id sk (surrogate key). replies original post id stored in same table in parent_post_id column, want perform following logic:

before insert (i think ...)

if parent_post_id has not been defined on insert, default row value newly generated post_id (from auto-int sequence)

if parent_post_id has been defined on insert, set whatever has been passed.

example

post_id | parent_post_id | date_time        | message      12               12   2015-04-14 21:10   new post (start of thread)      13               12   2015-04-14 21:12   reply post id 12 

the answer here: https://stackoverflow.com/a/11061766/1266457 looks might need do, although not sure it's doing.

thanks.

for before insert trigger can not last inserted primary key , other way of doing max value table , increment it.

here way it

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

so trigger check if there no value of parent_post_id in insert query max post_id. first entry null setting 1 i.e. , after max post_id + 1 after each entry.

here test case of in mysql

mysql> select * test ; empty set (0.00 sec)  mysql> delimiter // mysql> create trigger test_is before insert on test     -> each row      -> begin     ->   declare last_id int;      ->   if new.parent_id null     ->     select auto_increment last_id     ->     information_schema.tables table_name = 'test'     ->     , 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 test (val) values ('aa'); query ok, 1 row affected (0.10 sec)  mysql> insert test (val) values ('bb'); query ok, 1 row affected (0.04 sec)  mysql> select * test ; +---------+-----------+------+ | post_id | parent_id | val  | +---------+-----------+------+ |       1 |         1 | aa   | |       2 |         2 | bb   | +---------+-----------+------+ 2 rows in set (0.00 sec)