MySql insert trigger outputs NULL -

i want have insert trigger achieve incremental inserts. have 1 column "row_hash" record md5 of each row(here use md5 of column name , category). if new record has same hash value, trigger should skip insert, otherwise allows insert.

drop trigger if exists test_trigger; delimiter $$ create trigger test_trigger before insert on test_table each row begin     if (select count(*) test_table row_hash = md5(concat_ws('', new.name, new.category))) < 1         insert test_table(_id, name, category, row_hash) values (new._id, new.name, new.category, md5(concat_ws('', new.name, new.category)));     end if; end$$ 

here test table:

create table test_table(_id varchar(10) primary key, name varchar(10), category varchar(2), row_hash char(32)); 

when insert 1 record, outputs null on row_hash column.

insert test_table(_id, name, category) values('12344', 'dafas', 'a');  '12344','dafas','a',null 

can tell me did wrong on trigger?

if want modify row being inserted, don't need use insert same table. set new.row_hash values.

if want abort insert, you'll have use signal.

but overall, appears you're doing shouldn't require conditional insert in trigger @ all. unique constraint on name, category. in trigger, make sure row_hash set correctly, constraint take care of aborting if there's duplicate.

edit: changed unique key use row_hash column, suggested.

alter table test_table add unique key (row_hash);  create trigger test_trigger before insert on test_table each row set new.row_hash = md5(concat_ws('', name, category));