Geek asks and answers

MySQL starts column setup to max + 1, doesn't work

It's hard for me to write a trigger that sets the rank column to the maximum rank value plus 1 for a group of user IDs. Perhaps the code will be more useful than my description:

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 ;

Here is my startup 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

Here is the error message I get:

1064 - You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to '' on line 3

Any help would be greatly appreciated. I don't have much experience writing triggers.

MySQL server version: 5.1.49-3 ~ bpo50 + 1

+3
source share
1 answer

, mysql ; () CREATE TRIGGER

:

delimiter |


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;

|

delimiter ;
+8

More articles:


All Articles