50.9. 触发器(Trigger)-白红宇

50.9.1. create trigger

50.9.1.1. Update 更新出发

实现 history 历史表功能，BEFORE update 做到数据库更新自动备份

CREATE TABLE user_history SELECT * FROM user WHERE 1 <> 1DELIMITER //CREATE TRIGGER user_history BEFORE update ON user FOR EACH ROWBEGINinsert 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 ROWBEGIN	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

50.9.1.2. Delete 删除出发

CREATE DEFINER=`dba`@`%` TRIGGER `cms`.`jc_content_BEFORE_DELETE` BEFORE DELETE ON `jc_content` FOR EACH ROWBEGIN	insert into `neo`.elasticsearch_trash(id) values(OLD.content_id);END

50.9.1.3. Insert 插入出发

50.9.2. drop trigger

DROP TRIGGER admin_user_history;DELIMITER //CREATE TRIGGER admin_user_history BEFORE update ON admin_user FOR EACH ROWBEGINinsert into admin_user_history SELECT * FROM admin_user WHERE user_id = OLD.user_id;END; //DELIMITER;

50.9.3. show triggers

show triggers;

50.9.3.1. SHOW CREATE TRIGGER

mysql> SHOW CREATE TRIGGER ins_sum\G*************************** 1. row ***************************               Trigger: ins_sum              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONSQL 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.9.4. EXAMPLE

50.9.4.1. BEFORE/AFTER

例 50.1. BEFORE/AFTER

DROP TRIGGER MY_TEST_MONITOR;DELIMITER //CREATE TRIGGER MY_TEST_MONITOR BEFORE insert ON MY_TEST FOR EACH ROWBEGIN	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 ROWBEGIN	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.9.4.2. UUID

例 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$$CREATEDEFINER=`root`@`%`TRIGGER `test`.`member_before_insert`BEFORE INSERT ON `test`.`member`FOR EACH ROWSET new.uuid = uuid()$$

50.9.4.3. CALL PROCEDURE

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;ENDCREATE 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 系列手札

本文作者：陈景峯

转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。