Grrr... new trigger problem!

[ppetree]

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 ;

[smantscheff]

When I try this I get the error message Unknown column 'user_id' in 'NEW' Probably you got some field names wrong (user_id/userid).

My test case:

drop table if exists users;
create table if not exists users (
user_id integer not null primary key auto_increment,
lat decimal (12,7) not null,
lon decimal (12,7) not null
);
insert into users values (1,26.1962610 ,-80.1181660);


create table if not exists message( userid integer not null, occured date, message_text text, 
lat decimal (12,7) not null,
lon decimal (12,7) not null);

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 ;

INSERT INTO message (userid, occured, message_text, lat, lon) VALUES ('1', '2010-12-03', 'trigger test without lat/lon', '', '');

select * from message;

This code does what you want:

drop table if exists users;
create table if not exists users (
user_id integer not null primary key auto_increment,
lat decimal (12,7) not null,
lon decimal (12,7) not null
);
insert into users values (1,26.1962610 ,-80.1181660);


create table if not exists message( userid integer not null, occured date, message_text text, 
lat decimal (12,7) not null,
lon decimal (12,7) not null);

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.userid;
  END IF; 
  SET NEW.lat = user_lat;
  SET NEW.lon = user_lon;
END //

DELIMITER ;

INSERT INTO message (userid, occured, message_text, lat, lon) VALUES ('1', '2010-12-03', 'trigger test without lat/lon', 0, 0);

select * from message;

+--------+------------+------------------------------+------------+-------------+
| userid | occured    | message_text                 | lat        | lon         |
+--------+------------+------------------------------+------------+-------------+
|      1 | 2010-12-03 | trigger test without lat/lon | 26.1962610 | -80.1181660 |
+--------+------------+------------------------------+------------+-------------+
1 row in set

[ppetree]

You were absolutely dead on the money!!!

There were two issues: 1) user_id is the auto-increment record id in the users table and userid is the link-back reference in the message table (dont ask me why the original developer used two different names) and I was using user_id in both references which returned no records because there was never a match. 2) I was checking for ZERO and passing in NULL.

What is the correct way to check for NULL?

How can you debug triggers? Is there a log file or other resource that can give clues as to whats going on?

Thanks for all your help! I really, really appreciate it!

Pete

[smantscheff]

Check for null with the IS NULL test (http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html). Also have a look at ifnull function.
Debug triggers from the command line interface, not phpMyAdmin or any other higher level client. That way you'll get the unfiltered error messages.

[ppetree]

So you're saying this is correct?

IF lat IS NULL AND lon IS NULL THEN

I have mysql running on a desktop under windows 7... I think that desktop was never reassembled after we moved! LOL I guess I will now have to resurrect it.

Thanks again for all your help!

Merry Christmas!

[smantscheff]

Yes, the test clause is fine - as far as MySQL is concerned.
But if you are passing empty character strings '' or 0 to it, the test will fail. Maybe you should better test for both: IF ((lat IS NULL) [B]or (lat = 0) or (lat = ''))[/B] ... You don't have to install the mysql server, just the command line client (/usr/bim/mysql or mysql.exe) is fine if you have a server already running to which you can connect. The client is available for any flavor of OS.

[ppetree]

The geocoding function returns a structure and lat/lon are either set or not. If one is set, they both are set. If neither are set, the address cant be found (we check multiple sources in a most probable heirarchy) so if we get nothing back we'll fail the add so nothing should ever trigger this trigger EXCEPT when a record is added via an HTML page on the website itself. All other records are input via a series of SOAP servers for mobile devices, 911 services etc.)... in order to save old code we'll use the trigger to automatically provide the lat/lon that we're getting from the SOAP servers... should be an easy solution.