【原创】mysql 触发器trigger…

写了一个mysql trigger

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;

可是我复制到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

    if new.`play_time`=0 then

        set new.`state`=1, new.`play_time` = 20 * new.`play_num`;

    end if;

end$$