How to Create Triggers in MySQL

https://www.sitepoint.com/how-to-create-mysql-triggers/

 

I created two tables:

CREATE TABLE `sw_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT ‘‘,
`mobile` varchar(50) DEFAULT ‘‘,
`sex` tinyint(4) DEFAULT 0,
`address` varchar(255) DEFAULT ‘‘,
`status` tinyint(4) unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;

 

CREATE TABLE `sw_log`  (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `t_name` varchar(50) DEFAULT ‘‘,
  `t_id` int(11) unsigned DEFAULT 0,
  `t_flag` tinyint(4) unsigned DEFAULT 1,
  `status` tinyint(4) unsigned DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;

 

I created three triggers:

CREATE TRIGGER `sw_user_insert` AFTER INSERT ON `sw_user` FOR EACH ROW BEGIN
INSERT INTO sw_log(t_name, t_id, t_flag) values(‘sw_user‘, NEW.id, 1);
END;

CREATE TRIGGER `sw_user_update` AFTER UPDATE ON `sw_user` FOR EACH ROW BEGIN
INSERT INTO sw_log(t_name, t_id, t_flag) values(‘sw_user‘, OLD.id, 2);
END;

CREATE TRIGGER `sw_user_delete` AFTER DELETE ON `sw_user` FOR EACH ROW BEGIN
INSERT INTO sw_log(t_name, t_id, t_flag) values(‘sw_user‘, OLD.id, 3);
END;

 

first, I insert some records:

INSERT INTO `sw_user`(name, mobile, sex, address) VALUES(‘coffee‘, ‘1801863971x‘, 1, ‘Shenzhen‘);
INSERT INTO `sw_user`(name, mobile, sex, address) VALUES(‘sam‘, ‘1701892512y‘, 1, ‘Beijing‘);
INSERT INTO `sw_user`(name, mobile, sex, address) VALUES(‘bruce‘, ‘1391766716x‘, 1, ‘Nanjing‘);
INSERT INTO `sw_user`(name, mobile, sex, address) VALUES(‘vicky‘, ‘138982589x‘, 1, ‘Shanghai‘);

 

sw_user

sw_log

second, I update and delete one record

UPDATE `sw_user` SET mobile = ‘1567892587x‘ WHERE id = 1;
DELETE FROM `sw_user` WHERE id = 1;