触发器实例

CREATE TABLE `user_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_old_name` varchar(111) DEFAULT NULL,
  `user_new_name` varchar(111) DEFAULT NULL,
  `user_do_time` int(11) DEFAULT NULL,
  `user_type` varchar(111) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


触发器
1，当user表写入数据时，写入到user_log表中
---------------------------------------------
DELIMITER $$
CREATE TRIGGER user_insert_delimiter
AFTER INSERT
ON test.user FOR EACH ROW
BEGIN
    INSERT INTO test.user_log (user_new_name,user_do_time,user_type)
    VALUES (NEW.name,UNIX_TIMESTAMP(),'insert');
END
$$

---------------------------------------------



2，当user表更新数据时，写入到user_log表中
--------------------------------------------
DELIMITER $$
CREATE TRIGGER user_update_delimiter
AFTER UPDATE
ON test.user FOR EACH ROW
BEGIN
    INSERT INTO test.user_log (user_old_name,user_new_name,user_do_time,user_type)
    VALUES (OLD.name,NEW.name,UNIX_TIMESTAMP(),'update');
END
$$
---------------------------------------------

3，当user表删除数据时，写入到user_log表中
---------------------------------------------
DELIMITER $$
CREATE TRIGGER user_delete_delimiter
AFTER DELETE
ON test.user FOR EACH ROW
BEGIN
    INSERT INTO test.user_log (user_old_name,user_do_time,user_type)
    VALUES (OLD.name,UNIX_TIMESTAMP(),'delete');
END
$$
---------------------------------------------