MySQL學習筆記十一：觸發器-有解無憂

一、觸發器概念

觸發器是與表有關的資料庫物件，在滿足定義條件時觸發，并執行觸發器中定義的陳述句集合，

二、觸發器創建

2.1、創建語法

CREATE [DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

2.2、創建語法關鍵詞解釋

2.3、觸發執行內容OLD與NEW

OLD：表示將要洗掉的舊行（類似于SQL Server中的DELETED表，只不過前者是行記錄，后者是表），

NEW：表示將要插入的新行（類似于SQL Server中的INSERTED表，只不過前者是行記錄，后者是表），

注：因為是單行記錄，可以將它們理解為面向物件語言中的物件，直接通過`OLD.欄位名`和`NEW.欄位名`來使用，

事件與OLD、NEW的對應關系：

由上可見，更新一條記錄，實際上是先將原記錄洗掉（臨時保存在OLD中），再插入一條新的記錄（臨時保存在NEW中），

三、觸發器示例

3.1、觸發自身表

1）需求描述：假設有個員工表，如果新入職人員（INSERT）的年齡小于18歲時，自動將其更新為18歲，

2）測驗表創建：

DROP TABLE IF EXISTS EMP1;
CREATE TABLE `emp1` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3）觸發器創建：

#當年齡小于18歲時自動更新為18歲
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert BEFORE INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF (NEW.AGE<18) THEN
        SET NEW.AGE=18;
    END IF;
END$$
DELIMITER ;

4）資料插入：

INSERT INTO EMP1 VALUES (1,'HELLO',17);

5）結果查詢：

SELECT * FROM EMP1;

6）注意事項：

需要注意的是，MySQL觸發器不允許對自身表進行更新，但是允許更新NEW，

因此，上面的需求假如想通過AFTER INSERT來更改EMP1表，是行不通的，

下面進行錯誤的演示：

#錯誤的演示
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert AFTER INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF (NEW.AGE<18) THEN
        UPDATE EMP1 SET AGE=18 WHERE ID=NEW.ID;
    END IF;
END$$
DELIMITER ;

3.2、觸發其它表

1）需求描述：假設有兩個表結構一樣的表EMP1和EMP2，如果EMP1有新記錄插入時而EMP2還沒有，則將新記錄也插入到EMP2中，

2）測驗表創建：

DROP TABLE IF EXISTS EMP1;
CREATE TABLE `emp1` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

DROP TABLE IF EXISTS EMP2;
CREATE TABLE `emp2` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3）觸發器創建：

#EMP1插入新記錄而EMP2沒有時，將新記錄也插入到EMP2中，
DELIMITER $$
DROP TRIGGER IF EXISTS triEmp1ForInsert$$
CREATE TRIGGER triEmp1ForInsert AFTER INSERT ON EMP1 FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM EMP2 WHERE ID=NEW.ID) THEN
        INSERT INTO EMP2 (ID,`NAME`,AGE) VALUES (NEW.ID,NEW.NAME,NEW.AGE);
    END IF;
END$$
DELIMITER ;

4）資料插入：

INSERT INTO EMP1 VALUES (1,'HELLO',18);

5）結果查詢：

SELECT * FROM EMP2;

四、觸發器查看

#查看當前資料庫所有觸發器
SHOW TRIGGERS;
 
#查看指定資料庫所有觸發器
SHOW TRIGGERS FROM TEST;

#查看指定資料庫指定表所有觸發器
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_SCHEMA='TEST' AND EVENT_OBJECT_TABLE='EMP1';

五、觸發器洗掉

#直接洗掉觸發器
DROP TRIGGER triEmp1ForInsert;

#先檢查再洗掉觸發器
DROP TRIGGER IF EXISTS triEmp1ForInsert;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4084.html

標籤：MySQL

上一篇：事務的隔離級別與所帶來的問題

下一篇：MongoDB與SQL常用語法對應表