Trigger based on linked table
CREATE TRIGGER tr
BEFORE INSERT
ON tnt_item
FOR EACH ROW
SET NEW.itemNo = 1 + COALESCE((SELECT MAX(t1.itemNo)
                               FROM tnt_item t1, tnt_group t2, tnt_group t3
                               WHERE t1.groupID = t2.groupID
                                 AND t2.ClientID = t3.ClientID
                                 AND t3.groupID = NEW.groupID), 0);


fiddle