过程:设置触发次数,修改表flagtable中的kflag为5。向表tablename2写入数据,当它的fid为5的倍数时,将fid保存下来写入到tablename1表中现在问题是:我想写一个时间触发器,不用这个,请问事件触发器怎么写,定时在某一时刻‘13:00:00’触发,请问这个触发器该怎么写? 请大家帮帮忙?
插入数据表一
CREATE TABLE `tablename2` (
`fid` INT(19) NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`fid`)
) ENGINE=INNODB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8
表二 触发触发器后将 信息写入此表
CREATE TABLE `tablename1` (
`Cname` VARCHAR(50) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
表三 设置触发次数
CREATE TABLE `flagtable` (
`kFlag` INT(5) NOT NULL,
PRIMARY KEY (`kFlag`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
创建 触发器如下:
DELIMITER $$USE `test`$$DROP TRIGGER /*!50032 IF EXISTS */ `countTest`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `countTest` AFTER INSERT ON `tablename2`
FOR EACH ROW BEGIN
SET @i=new.fid;
SET @flage=1;
SELECT kFlag INTO @flage FROM flagTable;
IF @i%@flage=0 THEN
INSERT INTO tablename1 VALUES(@i);
END IF;
END;
$$DELIMITER ;
插入数据表一
CREATE TABLE `tablename2` (
`fid` INT(19) NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`fid`)
) ENGINE=INNODB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8
表二 触发触发器后将 信息写入此表
CREATE TABLE `tablename1` (
`Cname` VARCHAR(50) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
表三 设置触发次数
CREATE TABLE `flagtable` (
`kFlag` INT(5) NOT NULL,
PRIMARY KEY (`kFlag`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
创建 触发器如下:
DELIMITER $$USE `test`$$DROP TRIGGER /*!50032 IF EXISTS */ `countTest`$$CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `countTest` AFTER INSERT ON `tablename2`
FOR EACH ROW BEGIN
SET @i=new.fid;
SET @flage=1;
SELECT kFlag INTO @flage FROM flagTable;
IF @i%@flage=0 THEN
INSERT INTO tablename1 VALUES(@i);
END IF;
END;
$$DELIMITER ;
+-------+
| kFlag |
+-------+
| 5 |
+-------+
1 row in set (0.00 sec)mysql> select * from tablename2;
+-----+-------+
| fid | fname |
+-----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |
| 12 | 1 |
mysql> select * from tablename1;
+-------+
| Cname |
+-------+
| 5 |
| 10 |
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:· INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。· UPDATE:更改某一行时激活触发程序,例如,通过UPDATE语句。· DELETE:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。
create event event_name on schedule every 1 day starts timestamp (curdate()+interval 1 day,'13:00:00') on completion not preserve do SQL语句;
mysql> use test;
Database changed
mysql> create table test.t (s1 timestamp);
Query OK, 0 rows affected (0.11 sec)14mysql> create event e on schedule every 1 second do
insert into test.t values (current_timestamp);
Query OK, 1 row affected (0.00 sec)mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)/* 3-second delay */mysql> select * from test.t;
+---------------------+
| s1 |
+---------------------+
| 2006-04-05 15:44:26 |
| 2006-04-05 15:44:27 |
| 2006-04-05 15:44:28 |
+---------------------+谢谢你 我的版本是5.0.86 不支持这个http://dev.mysql.com/tech-resources/articles/mysql-events.html