This trigger is making me nuts! I've written about a dozen triggers before but nothing where I have ever pulled data from another table.
What this is supposed to do is make sure that any inserted rows have a latitude/longitude (lat/lon) value. The users table has the lat/lon and these are required fields and no chance of them EVER being NULL.
So, when I insert a record in to the message table using the userid of 1, a current date, some sample text and a blank lat/lon, the trigger should pull the lat/lon from the users table and update the NEW.lat/NEW.lon fields so that the new message record defaults to the users "home" location. If a lat/lon are in the row to be inserted then we need to keep those as the user is then in a "mobile" location (imagine a fire truck parked at their station as "home" and at a fire as "mobile").
Again, the problem is, nothing is getting pulled from the users table.
Any suggestions on how to fix this?
users table:
user_id lat lon
1 26.1962610 -80.1181660
insert statement
INSERT INTO message (userid, occured, message_text, lat, lon) VALUES ('1', '2010-12-03', 'trigger test without lat/lon', '', '')
Trigger:
DELIMITER //
CREATE TRIGGER `latlong_default` BEFORE INSERT ON `message`
FOR EACH ROW BEGIN
declare user_lat decimal(12,7);
declare user_lon decimal(12,7);
IF NEW.lat = 0 AND NEW.lon = 0 THEN
SELECT lat,lon INTO user_lat, user_lon FROM users WHERE user_id = NEW.user_id;
END IF;
SET NEW.lat = user_lat;
SET NEW.lon = user_lon;
END
DELIMITER ;