Resetting one column resets all

[Tom Donaldson]

I don't know whether I am stumbling on a bug, undocumented behavior, my own MySQL newbie ignorance, or am simply making a bonehead mistake. Ideas?

In a before-update trigger I check changed values. If they are out of the desired range I set the new value to the old value (i.e., SET new.latitude=old.latitude).

The problem I am running into is that resetting any changed column to its old value resets all columns. That is, resetting one column seems to prevent the row from being committed.

The following is a short script that illustrates my problem. It creates a very simple, stripped down table, creates a before-update trigger on the table, inserts a record, and attempts to update the record several times. It displays the contents of the table on each attempt. The trigger always modifies the comment field to indicate whether the changed latitude column (or attempted change to it) was good or bad. If the input latitude was bad, it resets the bad value to the previous value. When I run this script I never see any of the 'bad' comments; they are trashed when the latitude column is reset.

------------ BEGIN SQL SCRIPT ---------


-- MySQL version: 5.0.21-standard-log
-- Running on Mac OS X 10.4.6, dual 1.8MHz G5
--
-- Problem: Resetting one changed column in an updated record from a
-- before-update trigger causes all changes for the row to be lost.

CREATE TABLE `location` (
`location_id` int(10) unsigned NOT NULL auto_increment,
`latitude` double default NULL,
`comments` varchar(256) default NULL,
PRIMARY KEY (`location_id`)
);


DELIMITER //
CREATE TRIGGER location_update_before BEFORE UPDATE on location
FOR EACH ROW
BEGIN
-- If less than -180.0 degrees or greater than 180.0 degrees
-- comment on badness and reset to old value. Else, comment
-- on goodness and let the new value get stored.
CASE
WHEN new.latitude < -180.0 THEN
SET new.comments = 'Bad, too low';
SET new.latitude = old.latitude;

WHEN new.latitude > 180.0 THEN
SET new.comments = 'Bad, too high';
SET new.latitude = old.latitude;

WHEN new.latitude < 0.0 THEN
SET new.comments = 'Good, negative';

WHEN new.latitude = 0.0 THEN
SET new.comments = 'Good, zero';

ELSE
SET new.comments = 'Good, positive';
END CASE;
END
//
DELIMITER ;

-- Note that none of the 'Bad ...' comments will ever show up in
-- the table because whenever the new.latitude is set to the old.latitude
-- by the trigger, MySQL trashes ALL changes to the record.

INSERT INTO location SET latitude=-999.999, comments='Bogus starting record';
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=-888.888 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=-180.0 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=-777.777 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=0.0 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=777.777 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=180.0 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=888.888 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=117.119 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;

UPDATE location SET latitude=999.999 WHERE location_id=1;
SELECT * FROM location WHERE location_id=1;


------------ END SQL SCRIPT ---------

Mac/Unix Geek In a RV
www.mactom.com