Hi,
I have order_tier database and I am adding tiers by checking what's the max number then add 1 to it. Now I have a problem when deleting because if the order has 4 tiers then it should have from 1 to 4, if the user deleted tier 4 then it will be 1 to 3, if the user deleted tier 2 then tier 3 should be 2 and 4 should be 3.. By this I mean it should have always a sequence without a gap in the middle and it should move from the bottom so if user deleted tier 6 of 7 tier's order then tier 7 will be 6, etc.
How can I implement this in using PL/SQL in MySQL?
DECLARE param_last_tier INT;
IF EXISTS (SELECT tier_number FROM order_tiers WHERE order_id = param_order_id) THEN
BEGIN
SET param_last_tier = (SELECT MAX(tier_number) FROM order_tiers WHERE order_id = param_order_id) + 1;
END;
ELSE
BEGIN
SET param_last_tier = 1;
END;
END IF;
INSERT INTO order_tiers (order_id, tier_type, tier_number, created_user)
VALUES (param_order_id, param_tier_type, param_last_tier, param_created_user);
Thanks,
Jassim
