CREATE TRIGGER logNumberSet before INSERT ON login_log FOR EACH ROW BEGIN declare login_count int ; select count(*) into login_count from login_log ; if login_count > 10 then DELETE FROM login_log ORDER BY login_log.id LIMIT 5; end if; end;当我插入一条数据时 会报错 ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
因你的触发器里面进行了表自身删除操作“DELETE FROM login_log ORDER BY login_log.id LIMIT 5; ”,这个在mysql里是不允许的。(错误代码里面已经详细提示了:because it is already used by statement )
不要放在触发器中, 在系统中利用 linux crontabl / windows 计划任务 来定时删除。或者用 mysql 的 event 具体用法,请自行先查一下相关的帮助文档。
create table tb1( -> gid int auto_increment primary key, -> color int, -> size int -> ); Query OK, 0 rows affected (0.16 sec)mysql> mysql> create table tb2( -> id int auto_increment primary key, -> gid int, -> weight int -> ); Query OK, 0 rows affected (0.05 sec)mysql> mysql> create table tb3( -> id int auto_increment primary key, -> gid int, -> c3 int -> ); Query OK, 0 rows affected (0.08 sec)mysql> delimiter | mysql> mysql> CREATE TRIGGER t_tb1_ai AFTER INSERT ON tb1 -> FOR EACH ROW BEGIN -> insert into tb2(gid) values (new.gid); -> insert into tb3(gid) values (new.gid); -> END; -> | Query OK, 0 rows affected (0.11 sec)mysql> mysql> delimiter ; mysql> mysql> insert into tb1 (color) values (123); Query OK, 1 row affected (0.27 sec)mysql> select * from tb1; +-----+-------+------+ | gid | color | size | +-----+-------+------+ | 1 | 123 | NULL | +-----+-------+------+ 1 row in set (0.00 sec)mysql> select * from tb2; +----+------+--------+ | id | gid | weight | +----+------+--------+ | 1 | 1 | NULL | +----+------+--------+ 1 row in set (0.00 sec)mysql> select * from tb3; +----+------+------+ | id | gid | c3 | +----+------+------+ | 1 | 1 | NULL | +----+------+------+ 1 row in set (0.00 sec)
可以。 但不能对自身的表进行删除一半的操作。没有现成的例子,建议你先自己写一个,哪不对贴出来大家可以帮你一起看看。如果你连基本的触发器也不会写的话,则建议你参考一下MYSQL的官方手册中的说明和例子。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
FOR EACH ROW
BEGIN
declare login_count int ;
select count(*) into login_count from login_log ;
if login_count > 10 then
DELETE FROM login_log ORDER BY login_log.id LIMIT 5;
end if;
end;当我插入一条数据时 会报错
ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
不要放在触发器中, 在系统中利用 linux crontabl / windows 计划任务 来定时删除。或者用 mysql 的 event 具体用法,请自行先查一下相关的帮助文档。
-> gid int auto_increment primary key,
-> color int,
-> size int
-> );
Query OK, 0 rows affected (0.16 sec)mysql>
mysql> create table tb2(
-> id int auto_increment primary key,
-> gid int,
-> weight int
-> );
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> create table tb3(
-> id int auto_increment primary key,
-> gid int,
-> c3 int
-> );
Query OK, 0 rows affected (0.08 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER t_tb1_ai AFTER INSERT ON tb1
-> FOR EACH ROW BEGIN
-> insert into tb2(gid) values (new.gid);
-> insert into tb3(gid) values (new.gid);
-> END;
-> |
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into tb1 (color) values (123);
Query OK, 1 row affected (0.27 sec)mysql> select * from tb1;
+-----+-------+------+
| gid | color | size |
+-----+-------+------+
| 1 | 123 | NULL |
+-----+-------+------+
1 row in set (0.00 sec)mysql> select * from tb2;
+----+------+--------+
| id | gid | weight |
+----+------+--------+
| 1 | 1 | NULL |
+----+------+--------+
1 row in set (0.00 sec)mysql> select * from tb3;
+----+------+------+
| id | gid | c3 |
+----+------+------+
| 1 | 1 | NULL |
+----+------+------+
1 row in set (0.00 sec)