MySQL Trigger causing unknown system variable error on existing row -

i creating trigger in mysql , getting 'unknown system variable' on 'unique_id' row in 'pending_jobs' table. trigger code below:

create trigger format_pending_jobs_unique before insert on pending_jobs each row begin    set unique_id = concat(prefix_unique_id, id); end 

basically it's concatenating 2 rows (prefix_unique_id , id) row unique_id when new row inserted. prefix_unique_id row has default value of "sa" value of of them , id index of row auto increment. i'm new triggers , had read in post on stack overflow use := instead of = didn't fix problem. i'm using phpmyadmin , see unique_id (as prefix_unique_id , id) rows exists. great. thanks!

i write trigger way:

create trigger format_pending_jobs_unique before insert on pending_jobs each row   set new.unique_id =    concat(      new.prefix_unique_id,      (select auto_increment       information_schema.tables       table_schema=database()             , table_name='pending_jobs')    ); 

please see fiddle here.

new refers column of new row inserted, have use new.unique_id , new.prefix_unique_id. refer new value of id, can't use new.id need use subquery returns next auto_increment value table.