MySQL 5.1触发器小试

MySQL 5.1触发器小试

我一直以为MySQL 5.0的触发器好似没那么强大，被一个搞mssql的老大给忽悠了，其实MySQL触发器从5.0就变得很强大，当然包括5.1。

这里拿一个demo说事：

DROP TRIGGER IF EXISTS `ucenterhome`.`uchome_doing_test`;
DELIMITER //
CREATE TRIGGER `ucenterhome`.`uchome_doing_test` AFTER INSERT ON `ucenterhome`.`uchome_doing`
 FOR EACH ROW BEGIN
    INSERT INTO uchome_doing_1 SET doid= NEW.doid+3, uid= NEW.uid , username= NEW.username,`from`=NEW.`from`,dateline=NEW.dateline,message=NEW.message,ip=NEW.ip,replynum = NEW.replynum,mood=NEW.mood;   
END
//
DELIMITER ;

每向uchome_doing表中插一条数据，就复制到uchome_dong_1表中一条。

有两个关键字：

OLD  NEW

官方的解释：

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

还有两个关键字

BEFORE AFTER

官方解释

The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. The other allowable keyword here is AFTER.