写了一个mysql trigger
if new.`play_time`=0 then
set new.`state`=1, new.`play_time` = 20 * new.`play_num`;
end if;
create trigger before_insert_song_play_log
before insert on song_play_log
for each row
begin
if new.`play_time`=0 then
set new.`state`=1, new.`play_time` = 20 * new.`play_num`;
end if;
end;
一直报错,我了个擦擦的要不要这么耍我啊!
看了下版本5.5,找了下
官方文档,找到如下例子:
On Stop Action problems.
My solution was to create a unique/primary key on the table I want to insert into (in this case on S_ID), then if my sanity check fails I change the s_id to 0. This method will only ever create one duff record with an s_id of 0. Obviously you need to INSERT ignore!
CREATE TRIGGER sanityCheck
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF something THEN
SET NEW.S_ID = 0 ;
END IF;
END;
My solution was to create a unique/primary key on the table I want to insert into (in this case on S_ID), then if my sanity check fails I change the s_id to 0. This method will only ever create one duff record with an s_id of 0. Obviously you need to INSERT ignore!
CREATE TRIGGER sanityCheck
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF something THEN
SET NEW.S_ID = 0 ;
END IF;
END;
可是我复制到mysql也好,workbench也好,都报错,还是一样连着三个错误:
ERROR 1064 (42000): 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 11
mysql>
mysql>
end if;
ERROR 1064 (42000): 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 'end if' at line 1
mysql>
mysql>
end;
ERROR 1064 (42000): 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 'end' at line 1
然后在CSDN上发现一个解决方案:
超级简单的一个触发器怎么总是报错,帮忙看看
原来需要demimiter:
DELIMITER $$
create trigger before_insert_song_play_log
before insert on song_play_log
for each row
begin
end$$