想研究mysql的trigger很久了,今天试着写了第一个,基本上十分钟搞定,那感觉就是一个!
一个同事要求做一个改变本表记录时间的trigger,本来以为很难的,拿来mysql手册学习,试着做了一个,没思路,没办法,只有硬上了:
create trigger tri_name after update on t1 for each row update t1 set last_mod_date=now() where id=NEW.id;
竟然没任何错误,过了,那就试试吧,update一条语句,
竟然报错:
Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger;
google一下,高人指教,如果是update本表的数据,必须before,否则会死循环
这么简单的道理没有想到,而且给出了一个很好的例子,哈哈,改改那就是我的了:
create trigger testt1 before update on t1 for each row set NEW.stat=now() where id=NEW.id;
怒!!!!还报错:
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 'where id=NEW.id' at line 1
后来发现其实trigger很聪明的,不需要你限定各种条件,忽忽,
create trigger testt1 before update on t1 for each row set NEW.stat=now() ;
过了!测试一切ok!
阅读(604) | 评论(0) | 转发(0) |