MySQL触发器将列设置为最大+1无法正常工作 [英] MySQL trigger to set column to max + 1 not working
本文介绍了MySQL触发器将列设置为最大+1无法正常工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在编写触发器时遇到困难,该触发器将等级列设置为最大等级值加一组用户ID的1.也许代码比我的描述更有用:
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:
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 ;
这是我的触发代码:
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's the error message I'm getting:
1064-您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在第3行的''附近使用
任何帮助将不胜感激.我没有太多编写触发器的经验.
Any help would be greatly appreciated. I don't have much experience writing triggers.
MySQL服务器版本:5.1.49-3〜bpo50 + 1
MySQL Server version: 5.1.49-3~bpo50+1
推荐答案
这是因为mysql看到;
(分隔符)并中断了CREATE TRIGGER
That is because mysql sees ;
(the delimiter) and breaks the execution of 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 ;
这篇关于MySQL触发器将列设置为最大+1无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文