0

I have created a trigger in my MySQL database, which is meant to set the column named "gps_coordinates" to equal the column named "previous_gps_coordinates" before updating it with the new values.

The problem is on testing, I get the following error message:

Error Code: 1054. Unknown column 'gps_coordinates' in 'field list'

My table structure is as follows:

|Location Number| gps_coordinates | previous_gps_coordinates
|105285         |Value can be NULL| Value can be NULL
|125896         |Value can be 0   | Value can be 0

My Trigger is the following:

DELIMITER $$

CREATE TRIGGER GPSUPDATE
BEFORE UPDATE 
ON sys_reference.location_reference
FOR EACH ROW 

BEGIN   
IF NEW.previous_gps_coordinates >= 0 THEN   
SET NEW.previous_gps_coordinates := gps_coordinates;    
END IF;
END$$
DELIMITER ;

I have also tried the variation of: 

DELIMITER $$

CREATE TRIGGER GPSUPDATE
BEFORE UPDATE 
ON sys_reference.location_reference
FOR EACH ROW 

BEGIN   
IF NEW.previous_gps_coordinates IS NULL THEN    
SET NEW.previous_gps_coordinates := gps_coordinates;    
END IF;
END$$
DELIMITER ;

Any help will be appreciated here. Thanks

Leon Claassen
  • 183
  • 3
  • 12
  • 1
    Think you need to use OLD... `SET NEW.previous_gps_coordinates := OLD.gps_coordinates;` – Raymond Nijland May 29 '18 at 12:21
  • Thanks @RaymondNijland that worked. I saw someone make mention of using `OLD` on a post somewhere, but I didn't use it because unlike the `NEW.` `OLD.` does not get highlighted in blue. Except now I am getting `Error Code: 1292. Truncated incorrect DOUBLE value: '-26.078659 27.758341'` when running an update to a column that has actual co-ordinates. – Leon Claassen May 29 '18 at 12:23
  • Solved this now... Changed the `>= 0` to `IS NOT NULL` and have a second TRIGGER setup for `IS NULL` – Leon Claassen May 29 '18 at 12:36

0 Answers0