title: MySQL trigger to set column to max + 1 not working
body: <p>I'm having difficulty writing a trigger that sets the rank column to the max rank value plus 1 for a group of user ids.  Maybe the code would be more helpful than my description:</p>
  <pre><code>CREATE TABLE `saved_listing` (
    `saved_listing_id` int(10) NOT NULL auto_increment,
    `user_id` int(10) NOT NULL default '0',
    `listing_id` int(10) NOT NULL default '0',
    `listing_ty` varchar(10) NOT NULL default '',
    `notes` text NULL,
    `rank` int(10) NOT NULL default '0',
    `modify_by` int(10) NOT NULL default '1',
    `modify_dt` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `create_by` int(10) NOT NULL default '1',
    `create_dt` datetime NOT NULL default '0000-00-00 00:00:00',
    `active` enum('Yes','No') NOT NULL default 'No',
    PRIMARY KEY  (`saved_listing_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
  </code></pre>
  <p>Here's my trigger code:</p>
  <pre><code>CREATE TRIGGER ins_saved_listing BEFORE INSERT ON saved_listing
  FOR EACH ROW BEGIN
      SET NEW.create_dt = NOW();
      SET NEW.rank = (SELECT MAX(rank) + 1 FROM saved_listing WHERE user_id = NEW.user_id);
  END
  </code></pre>
  <p>Here's the error message I'm getting:</p>
  <blockquote>
  <h1>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</h1>
  </blockquote>
  <p>Any help would be greatly appreciated.  I don't have much experience writing triggers.</p>
  <p>MySQL Server version: 5.1.49-3~bpo50+1</p>