In MySQL trigger, is it possible to set a user variable with NEW.col and use that in update query? -

thanks in advance help. looked on , couldn't find example quite i'm needing with. i'm creating trigger update table after insert don't know table name update until after insert happens. code i'm trying use, error.

begin   set @ven = new.`ven_code`;   set @ventable = concat('pp_ven_',@ven);   update @ventable set `stock_qty`=new.`endingstock` `iin`=new.`iin`; end 

this not possible described dynamical sql / prepared statement. generate error code: 1336. dynamic sql not allowed in stored function or trigger upon attempt create trigger.

about closest automation use create event. events scheduled stored programs run on schedule / interval of choosing. intervals are:

interval:

quantity {year | quarter | month | day | hour | minute |           week | second | year_month | day_hour | day_minute |           day_second | hour_minute | hour_second | minute_second} 

your set "flag" speak on row, such table depicting above has after insert trigger. event perform prepared statement dynamically , execute it.

see answer here on event management.

i have if run in event, proposing always sign of poor schema design wouldn't hold peer review.

a reason why dynamic sql , prepared stmt disallowed because trigger needs fast, , ddl snuck string , executed. , ddl stmts alter table disallowed in triggers(they take hours run literally).

your schema have 1 table shared column ven_code being differentiator column. instead chose create new tables each ven_code. typically poor design , performance choice.

if need schema design, happy chat in chat room.