一、建表
testb_log表
/* test库下建立testb_log表 */CREATE TABLE `test`.`testb_log` (`ID` int(8) NOT NULL AUTO_INCREMENT,`TESTB_ID` int(11) DEFAULT NULL,`NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`AGE` int(11) DEFAULT NULL,`STATUS` int(1) DEFAULT NULL,`NEW_STATUS` int(1) DEFAULT NULL,`ACTION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`TIME` datetime(0) DEFAULT NULL,PRIMARY KEY (`ID`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
testb表见MySQL存储过程之游标的使用
二、触发器之insert
delimiter $$CREATE TRIGGER tr_testb_insert AFTER INSERT ON testb FOR EACH ROWBEGININSERT INTO testb_log (TESTB_ID,`NAME`, AGE,`STATUS`,`ACTION`,TIME)VALUES(NEW.ID, NEW.NAME, NEW.AGE, NEW.STATUS,'INSERT',NOW());END;$$
三、触发器之update
delimiter $$CREATE TRIGGER tr_testb_update AFTER UPDATE ON testb FOR EACH ROWBEGININSERT INTO testb_log (TESTB_ID,`NAME`, AGE,`STATUS`,`ACTION`,TIME, NEW_STATUS)VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.STATUS, 'UPDATE', NOW(), NEW.STATUS);END;$$
四、触发器之delete
delimiter $$CREATE TRIGGER tr_testb_delete BEFORE DELETE ON testb FOR EACH ROWBEGININSERT INTO testb_log (TESTB_ID,`NAME`, AGE,`STATUS`,`ACTION`,TIME)VALUES(old.ID, OLD.NAME, OLD.AGE, OLD.STATUS,'DELETE',NOW());END;$$
五、其他
/*查看已启用的触发器*/SHOW TRIGGERS;/*删除名为tr_testb_insert的触发器*/DROP TRIGGER tr_testb_insert;
六、验证
①插入一条数据
INSERT INTO testb ( ID, NAME, AGE, STATUS ) VALUES ( 1, '小明', 11, 0 )
testb_log表显示如下：
②更新一条数据
UPDATE testb SET `STATUS`='1' WHERE ID=1
testb_log表显示如下：
③删除一条数据
DELETE FROM testb WHERE ID=1
testb_log表显示如下：
如果觉得《MySQL触发器之insert update delete的使用》对你有帮助，请点赞、收藏，并留下你的观点哦！
