Because you're fetching maximum among where BL_Lno is from provided value.
and in the your scenario you need to find MAX from all record.
having a value of 9
and you're incrementing it with 10
, But do you know that there's alerady a record for ID 9
If you want to generate the incremental no then don't go with custom generation, USE Auto Increment column
] that will take care itself.