MySQL Trigger - AFTER UPDATE - uses cached stale data

Question

My system has two tables products and prod_item as below.

  CREATE TABLE `products` (
      `prod_id` int(11) NOT NULL,
      `prod_desc` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
      `cat_type` varchar(50) COLLATE utf8mb4_unicode_520_ci NOT NULL,
      `stock` int(11) NOT NULL,
      `instock` tinyint(1) NOT NULL DEFAULT 0,
      `date_added` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;



CREATE TABLE `prod_item` (
  `prod_code` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `prod_id` int(11) NOT NULL,
  `supplier` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `serial` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `prod_condition` enum('New','Old','Damaged','Lost') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'New',
  `availability` enum('available','no stock','pick-up','delivery','reserved') COLLATE utf8mb4_unicode_ci NOT NULL,
  `location` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I have the below TRIGGER defined on products table on update of a product.

CREATE TRIGGER `upd_cat_code` 
    AFTER UPDATE ON `products` 
    FOR EACH ROW 
        BEGIN
            DECLARE vcOldCode VARCHAR(50);
            DECLARE vcNewCode VARCHAR(50);
            IF ((! (NEW.cat_type <=> OLD.cat_type)) )
            THEN
                SELECT LEFT(prod_code, LENGTH(prod_code) - 4) INTO vcOldCode FROM prod_item WHERE prod_id =  NEW.prod_id LIMIT 1;
                  
                SELECT CONCAT(cat_type_code, "-", MAX(item_num) + 1, "-")
                    INTO vcNewCode
                FROM 
                    (SELECT cat_type, cat_type_code FROM prod_cat WHERE cat_type = NEW.cat_type) codes 
                LEFT JOIN   (
                    SELECT
                        p.prod_id, prod_code, 
                        CAST(SUBSTR(prod_code, 15, 4) AS UNSIGNED) AS item_num,
                        p.cat_type                                                                             ,
                    FROM  products p, prod_item pi
                    WHERE p.prod_id = pi.prod_id
                ) prod
                    ON codes.cat_type  = prod.cat_type;   
                
                UPDATE
                    prod_item
                    SET prod_code = REPLACE(prod_code, vcOldCode, vcNewCode)
                WHERE prod_id = NEW.prod_id;
            END IF;
        END

When I do an update on category (cat_type) in products table, the trigger gets fired as expected and the prod_code of prod_item table gets updated as expected. However when the same product row in products table gets updated again, the trigger gets fired, but MAX on item_num returns the value that was generated in the previous run of the trigger (or previous update). Essentially cached value is being picked in subsequent updates. Please see the scenario below for better understanding.

Sample rows from products table.

+----------------------------------------------------------------------+
|   prod_id   |   prod_desc   |   cat_type   |   stock   |   instock   |
+----------------------------------------------------------------------+
|      1      | fountain pen  |  ST_W_01     |   3209    |     1       |
|      2      | art pencils   |  ST_W_02     |     92    |     1       |
|      3      | rucksack      |  BG_C_01     |    455    |     1       |
+----------------------------------------------------------------------+

If the cat_type is changed, the trigger is fired to update the prod_code in prod_item table. prod_code contains a serial number within the category which is derived as given in the trigger query. It's incremented for each product added in a category. On category change, product moves from one category and get added to another category. However when the update happens one after another for the same product for correction due to wrong code assigned in the first attempt, the serial number is fetched for OLD category and not for the NEW. So for example, a cat_type "ST_W_01" has max serial number of 912, "ST_W_02" has a max serial number of 1405 and "BG_C_01" has a max serial number of 312. When I change from "ST_W_01" to "ST_W_02", the serial number for prod_code becomes 1406 as expected. When I change it again from "ST_W_02 to "BG_C_01", the serial number becomes 1407 instead of 313 as it picks the previous "ST_W_02" cat_type's serial and increments. Can this forum help me to fix this? Thanks.