mysql5.1触发器小试
08-25
我一直以为mysql5.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.
