Trigger Syntax problem

[ppetree]

I'm having problems creating the following trigger as I keep getting the syntax error listed below the sql code and well, that error is about as clear as mud! LOL

Two things to note: users is a valid table name and it does contain fields called lat & lon (latitude and longitude) and these are defined as DECIMAL(12,7). Also, this table has a trigger BEFORE UPDATE on.

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

Error
SQL query: 

CREATE TRIGGER `latlong_default` BEFORE INSERT ON `message` 
FOR EACH
ROW 
BEGIN declare user_lat decimal( 12, 7 ) ;


MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Any help would be GREATLY appreciated.

[smantscheff]

Looks like your delimiter statement is not accepted. Maybe it's filtered by some library layer in between? Did you enter the code at the MySQL command line client?

[ppetree]

Looks like your delimiter statement is not accepted. Maybe it's filtered by some library layer in between? Did you enter the code at the MySQL command line client?

No, through phpMyAdmin which is the only way I have of interfacing with the database (hosted).

[smantscheff]

Install a local copy of MySQL and your database which you can download via phpMyAdmin and try the command line client with your trigger.
I strongly assume that phpMyAdmin interferes with the redefinition of the delimiter.

[ppetree]

I think you're right.

Here's the SQL code that works:

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

DELIMITER ;

All I really did was put a [CR] between the DELIMITER statements and the trigger code. Interesting little gotcha.

Good catch on your part and thanks for the help!

Pete