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

Question

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.

Abhik Chakraborty · Accepted Answer · 2015-04-14T21:25:32.250

2

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)

edited Apr 14 '15 at 21:25

answered Apr 14 '15 at 20:42

Abhik Chakraborty

44,654
6
52
63

Thank you Abhik. I was hoping it could get the sequence ID and use it. Using the MAX(post_id) + 1 could work, but would it be reliable? For example - say they current sequence is on 42, then some uncommitted stuff comes in and the next sequence is now 48 - I'd potentially be storing it as 43 (if that makes sense). – ash Apr 14 '15 at 20:47
1

well if the transaction is uncommitted then theoretically the table will be lock and the new insert will not happen until the transaction is complicated. However you can use the following command to get the next id as `SELECT auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'tablename'` this is much better than using max – Abhik Chakraborty Apr 14 '15 at 20:52
I've used this - but it's still setting parent_post_id to 0: `BEGIN DECLARE prev_post_id INT; IF NEW.parent_post_id IS NULL THEN SELECT auto_increment INTO prev_post_id FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'posts'; SET NEW.parent_post_id = prev_post_id; END IF; END` – ash Apr 14 '15 at 21:12
Just realised - it's because I hand't set it to NULL in my INSERT query. (and it default to 0) - doh! – ash Apr 14 '15 at 21:23
1

If you are using the `INFORMATION_SCHEMA.TABLES` then null check is not needed. Check my updated test case. Also you may add the database name also into the query along with table name, so that it looks for that table into the specified database. – Abhik Chakraborty Apr 14 '15 at 21:26
Thank you very much Abhik for taking the time to do this - it's almost working, I just have to set my parent_post_id to NULL in the INSERT statement if it isn't set, else it'll set it as NULL. Thank you again :) – ash Apr 14 '15 at 21:48

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

1 Answers1