实现 history 历史表功能，BEFORE update 做到数据库更新自动备份
CREATE TABLE user_history SELECT * FROM user WHERE 1 <> 1 DELIMITER // CREATE TRIGGER user_history BEFORE update ON user FOR EACH ROW BEGIN insert into user_history SELECT * FROM user WHERE id = OLD.id; END; // DELIMITER ;
判断某字段数据修改满足条件后出发。
CREATE DEFINER=`dba`@`%` TRIGGER `cms`.`jc_content_BEFORE_UPDATE` BEFORE UPDATE ON `jc_content` FOR EACH ROW
BEGIN
IF NEW.status = '1' THEN
insert into `neo`.elasticsearch_trash(id) values(OLD.content_id);
END IF;
IF NEW.status = '2' THEN
delete from `neo`.elasticsearch_trash where id = OLD.content_id;
END IF;
END
CREATE DEFINER=`dba`@`%` TRIGGER `cms`.`jc_content_BEFORE_DELETE` BEFORE DELETE ON `jc_content` FOR EACH ROW BEGIN insert into `neo`.elasticsearch_trash(id) values(OLD.content_id); END
DROP TRIGGER admin_user_history; DELIMITER // CREATE TRIGGER admin_user_history BEFORE update ON admin_user FOR EACH ROW BEGIN insert into admin_user_history SELECT * FROM admin_user WHERE user_id = OLD.user_id; END; // DELIMITER;
show triggers;
mysql> SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum
BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
Created: 2013-07-09 10:39:34.96
例 50.1. BEFORE/AFTER
DROP TRIGGER MY_TEST_MONITOR; DELIMITER // CREATE TRIGGER MY_TEST_MONITOR BEFORE insert ON MY_TEST FOR EACH ROW BEGIN INSERT INTO MY_TEST_MONITOR SELECT * FROM MY_TEST WHERE TICKET = NEW.TICKET; END; // DELIMITER;
DROP TRIGGER MY_TEST_MONITOR; DELIMITER // CREATE TRIGGER MY_TEST_MONITOR AFTER insert ON MY_TEST FOR EACH ROW BEGIN INSERT INTO MY_TEST_MONITOR SELECT * FROM MY_TEST WHERE TICKET = NEW.TICKET; END; // DELIMITER;
通过触发器保护数据，防止重复插入数据
CREATE DEFINER=`neo`@`%` TRIGGER `members_before_insert` BEFORE INSERT ON `members` FOR EACH ROW BEGIN IF new.username IS NOT NULL THEN IF exists(select m.username from members m where m.username = new.username) THEN set new.username = ''; END IF; END IF; END
例 50.2. uuid()
delimiter $$ CREATE TABLE `member` ( `uuid` char(36) NOT NULL, `username` varchar(20) DEFAULT NULL, `password` varchar(32) DEFAULT NULL, PRIMARY KEY (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ CREATE DEFINER=`root`@`%` TRIGGER `test`.`member_before_insert` BEFORE INSERT ON `test`.`member` FOR EACH ROW SET new.uuid = uuid() $$
CREATE DEFINER=`neo`@`%` TRIGGER `accounts_angelfund` AFTER INSERT ON `accounts` FOR EACH ROW BEGIN IF new.paymode = 'angelfund' THEN call angelfund(new.name,new.ctime); END IF; END CREATE DEFINER=`neo`@`%` PROCEDURE `angelfund`(IN `puid` VARCHAR(50), IN `ptime` DATETIME) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE fusername VARCHAR(16) DEFAULT NULL; DECLARE fchinese_name VARCHAR(16) DEFAULT NULL; DECLARE fmembers_date VARCHAR(20) DEFAULT NULL; SELECT username,chinese_name,FROM_UNIXTIME(createtime) INTO fusername,fchinese_name,fmembers_date FROM members WHERE username = puid; IF fusername IS NOT NULL THEN INSERT IGNORE INTO angelfund(username,chinese_name,members_date,accounts_date,endtime,`status`,op,operator,`description`) value(fusername,fchinese_name,fmembers_date,ptime,DATE_ADD(ptime, INTERVAL +1 MONTH),'N','N','computer',''); END IF; END
原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
