1.建立表脚本
CREATE DATABASE `spring` DEFAULT CHARACTER SET utf8; USE `spring`; CREATE TABLE `account` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`birthday` datetime NOT NULL,
`email` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; CREATE TABLE `account_flow` (
`id` int(11) DEFAULT NULL,
`username` varchar(45) DEFAULT NULL,
`add_time` datetime DEFAULT CURRENT_TIMESTAMP,
`flag` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.建立触发器
DELIMITER $$
CREATE TRIGGER `account_insert_trigger` AFTER INSERT ON `account` FOR EACH ROW BEGIN
INSERT INTO account_flow (id,username,flag)
VALUES (new.id, new.username, 1);
END
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `account_update_trigger` AFTER UPDATE ON `account` FOR EACH ROW BEGIN
if old.username != new.username then
INSERT INTO account_flow (id,username,flag)
VALUES (new.id, new.username, 2);
end if;
END
DELIMITER ;
3.触发器说明 （注意new,old关键字）
account_insert_trigger： 添加account记录的时候，同时添加一条记录到account_flow表中。
account_update_trigger：修改account记录的时候，判断username是否修改，如果修改同时在account_flow表中添加一条记录。