Mysql TRIGGER not taking function returned value on update

Question

I have this Query which tries to update several values in another table after a insertion has been commited. Inside the trigger there is a function called calc_distance which returns a DOUBLE type value.

So, the problem is that no matter if I call the function properly the update is not taking the returned value. I tested the function running it separately from the mysql command prompt and in fact it returned the expected calculation.

So can someone please tell me what's going on on this query?

    DELIMITER ;;
    TRIGGER `DB1`.`update_coords`
    AFTER INSERT ON `DB1`.`TABLE_tracking`
    FOR EACH ROW
    BEGIN
    DECLARE LAT1, LAT2, LNG1, LNG2, DISTANCE DOUBLE;
    UPDATE TABLE_vehiculos SET ultima_comunicacion = now(),
    googlelat=(IF(NEW.latitude<>0,NEW.latitude,googlelat)), googlelong=(IF(NEW.longitude<>0,NEW.longitude,googlelong)),
    tipo_data=NEW.command, reported_command=(IF(NEW.valid=1,"F","A")), velocidad=NEW.speed, status="A",

    /* HERE COMES THE FUNCTION THAT IS NOT DOING ANYTHING */
    odometro=odometro+calc_distance(googlelat,NEW.latitude,googlelong,NEW.lontitude)
    /* --------------------------------------------------- */

    WHERE imei = NEW.device_id;
    END ;;
    DELIMITER ;

I declared LAT1, LAT2, LNG1, LNG2, DISTANCE to set all those variables with NEW.latitude and so on, but I realized that I cannot do anything with those variables because I cannot access NEW.latitude and the values googlelat or googlelong until I call update tableX, so, what is going on? I tested the calc_distance function like this:

    mysql> select calc_distance(9.104550000000, 9.102577000000, -79.370780000000, -79.3622423000000);
    +------------------------------------------------------------------------------------+
    | calc_distance(9.104550000000, 9.102577000000, -79.370780000000, -79.3622423000000) |
    +------------------------------------------------------------------------------------+
    |                                                                 0.9627214584265752 |
    +------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

Please I've been hitting my head against the monitor for the last 6 hours, please help!!

    DELIMITER ;;
    CREATE FUNCTION `calc_distance`(`lat1` DOUBLE, lat2 DOUBLE, lng1 DOUBLE, lng2 DOUBLE)
    RETURNS DOUBLE
    BEGIN
        DECLARE radio INTEGER(5);
        DECLARE distance DOUBLE;
        set radio = 6371;
            SELECT radio * 2 * ASIN
            ( SQRT (POWER(SIN((lat1 - lat2)*pi()/180 / 2),2) +
            COS(lat1 * pi()/180) * COS(lat2 *pi()/180) *
            POWER(SIN((lng1 - lng2) *pi()/180 / 2), 2) ) ) into distance;
        RETURN distance;
    END ;;
    DELIMITER ;

Are you sure the problem is with this function, and not with the surrounding query? Your updating one column (eg. googlelat) and then reading its value later on inside the same statement looks fishy to me. — RandomSeed
– RandomSeed, Commented Sep 25, 2013 at 6:10
Well the trigger without the calc_distance call is running smoothly and is working perfectly... is updating all fields. now the calc_distance function is not working, the odometro field remains the same. When i say is not working, I'm refering to the fact that the update is not taking the result returned from the calc_distance function, in fact if you add odometro=odometro+googlelat+googlelong for example, it works... is the function calc_distance that is not working, however, if you call select calc_distance and give the required params, it returns the result without issues. — Luis Parada
– Luis Parada, Commented Sep 25, 2013 at 6:17
What I mean is: are you sure the function is receiving the expected values for its parameter googlelat abd googlelong ? Log their values from within your function. — RandomSeed
– RandomSeed, Commented Sep 25, 2013 at 6:19
I also checked if is receiving the params, very easily,. I did odometro=odometro+googlelat+googlelong+NEW.latitude+NEW.longitude and the result is in fact the sumatory of all involved fields, which means, is working... — Luis Parada
– Luis Parada, Commented Sep 25, 2013 at 6:22

peterm · Accepted Answer · 2013-09-25 06:05:21Z

1

If I understand correctly try

CREATE TRIGGER `DB1`.`update_coords`
AFTER INSERT ON `DB1`.`TABLE_tracking`
FOR EACH ROW
    UPDATE TABLE_vehiculos 
       SET ultima_comunicacion = NOW(),
           googlelat  = IF(NEW.latitude <> 0, NEW.latitude, googlelat), 
           googlelong = IF(NEW.longitude <> 0, NEW.longitude, googlelong),
           tipo_data  = NEW.command, 
           reported_command = IF(NEW.valid = 1, 'F', 'A'),
           velocidad  = NEW.speed, 
           status = 'A',
           odometro = odometro + 
                      calc_distance(IF(NEW.latitude <> 0, NEW.latitude, googlelat),
                                    NEW.latitude,
                                    IF(NEW.longitude <> 0, NEW.longitude, googlelong),
                                    NEW.lontitude)
     WHERE imei = NEW.device_id;

FYI: Now it's a one-statement trigger so you don't need to change DELIMITER and use BEGIN...END block.

answered Sep 25, 2013 at 6:05

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luis Parada Over a year ago

Nop, is not updating the field odometro. Is still the same. sorry.

Luis Parada Over a year ago

please see the update and check if the function is the problem.

Luis Parada · Accepted Answer · 2013-09-26 06:54:26Z

0

This is the best solution, instead of trying to update from another table, why not update from the affected table directly?

    DELIMITER ;;
    CREATE TRIGGER `DB1`.`update_distances`
    BEFORE UPDATE ON `DB1`.`tb_vehiculos`
    FOR EACH ROW
    BEGIN
    SET @DISTANCE = calc_distance(OLD.googlelat,NEW.googlelat,OLD.googlelong,NEW.googlelong);
    SET NEW.odometro = OLD.odometro + @DISTANCE;
    END ;;
    DELIMITER ;

answered Sep 26, 2013 at 6:54

Luis Parada

1182 silver badges15 bronze badges

Collectives™ on Stack Overflow

Mysql TRIGGER not taking function returned value on update

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related