4.9. 觸發器(Trigger)

4.9. 觸發器(Trigger)
上一頁	第 4 章 DDL - Data Definition Language	下一頁

4.9.1. create trigger

4.9.1.1. Update 更新出發

實現 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

4.9.1.2. Delete 刪除出發

			
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

4.9.1.3. Insert 插入出發

4.9.2. drop trigger

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;

4.9.3. show triggers

show triggers;

4.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_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

4.9.4. EXAMPLE

4.9.4.1. BEFORE/AFTER

例 4.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

4.9.4.2. UUID

例 4.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()
$$

4.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;

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

上一頁	上一級	下一頁
4.8. 函數	起始頁	4.10. 事件調度器(EVENT)