小弟,想建个触发器,监控表的记录数(insert时触发),超过一定条数就删除表的前一半记录,一个触发器可以监控多个表吗?那个大哥给个例子,谢谢
解决方案 »
- linux下mysql乱码
- 怎么在不重启情况下删除Mysql当前二进制日志中某个时间段的记录
- 关于MYSQL的多条件模糊查询
- 我没有分,但我需要请求帮助,谢谢
- 自从安装了mysqlodbc3.51.12后,odbc出故障了,连接任何数据库都非常慢
- 请教结果集作为连接对象
- hanlderscoket安装配置make时出问题
- help!怎样加入mysql的jdbc driver(mm.mysql)进如jb5和weblogic?
- 【请教】关于mysql设置了unique key被插入重复记录的疑惑
- 多表查询的写法,各表之间有外键约束
- 求MSSQL的储存过程转换为MYsql!
- 编程来比较2个库之间的表数目是否相同 表结构是否相同
可以。 但不能对自身的表进行删除一半的操作。没有现成的例子,建议你先自己写一个,哪不对贴出来大家可以帮你一起看看。如果你连基本的触发器也不会写的话,则建议你参考一下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)