Create function within MYSQL to change data in the same table

Question

Hello.

I have an application that inserts data into a table positions, in several columns, I need to take this data work them and write a new value in the column odo, that column the application does not provide me.

I’m trying to do this with triggers and Procedures but Mysql says that I cannot change data in the same table you are entering.

The trigger I’m wearing is after Insert and the before Insert.

and presents the following error::

Can't update table 'positions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

How can I do?

CREATE DEFINER=`root`@`localhost` PROCEDURE `atualizaOdo`(IN `positionid` BIGINT(20), IN `deviceid` BIGINT(20), IN `valid` TINYINT(1), IN `latitude2` DOUBLE, IN `longitude2` DOUBLE, IN `altitude` DOUBLE, IN `speed` DOUBLE, IN `course` DOUBLE, IN `status` VARCHAR(150), IN `door` VARCHAR(30), IN `acc` TINYINT(1), IN `fuel` VARCHAR(50), IN `timeN` BIGINT(20), IN `time2` BIGINT(20)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN

set @posi = (SELECT `p1` FROM `positions` WHERE `id` < positionid and `device_id` = deviceid ORDER BY `id` DESC LIMIT 1);

    set @odo = (SELECT `odometroTotal` FROM `odometro` WHERE `device_id` = deviceid);
    
    
    set @earth_radius = 6372.797560856;
    
    set @fator = (SELECT `fator` FROM `devices_detalhes` WHERE `device_id` = deviceid);


    
    set @latitude1 = (SELECT `latitude` FROM `positions` WHERE `id` = @posi);
    set @longitude1 = (SELECT `longitude` FROM `positions` WHERE `id` = @posi);
    
    
    set @dLat = radians(latitude2 - @latitude1);
    set @dLon = radians(longitude2 - @longitude1);

    set @a = asin(@dLat/2) * asin(@dLat/2) + acos(radians(@latitude1)) * acos(radians(latitude2)) * asin(@dLon/2) * asin(@dLon/2);
    set @c = 2 * asin(sqrt(@a));
    set @d = (@earth_radius * @c)*(@fator + 1);
    set @odoT = (@d + @odo);
    
    
     INSERT INTO `positions`(`id`, `device_id`, `valid`, `latitude`, `longitude`, `altitude`, `speed`, `course`, `status`, `door`, `acc`, `fuel`, `time`, `time2`, `odo`, `p1`) VALUES (positionid, deviceid, valid, latitude2, longitude2, altitude, speed, course, status, door, acc, fuel, timeN, time2, @d, @posi);
     
     UPDATE `odometro` SET `odometroTotal` = @odoT, `positionId` = positionid, `time` = timeN WHERE `device_id` = @deviceid;
     
     
            
END