MySQL學習筆記—觸發程式

觸發程式是與表有關的命名資料庫對象，當表上發生特定事件時，将觸發執行相應的觸發程式。

CREATE TRIGGER文法

CREATE TRIGGER文法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name 
   FOR EACH ROW 
   trigger_stmt
           

觸發程式是與表有關的命名資料庫對象，當表上出現特定事件時，将激活該對象。

觸發程式與命名為tbl_name的表相關。tbl_name必須引用永久性表。不能将觸發程式與TEMPORARY表或視圖關聯起來。

trigger_time是觸發程式的動作時間。它可以是BEFORE或AFTER，以指明觸發程式是在激活它的語句之前或之後觸發。

trigger_event指明了激活觸發程式的語句的類型。trigger_event可以是下述值之一：

1. 1.

插入資料：将新行插入表時激活觸發程式，例如，通過INSERT、LOAD DATA和REPLACE語句。

1. 2.

更新資料：更改某一行時激活觸發程式，例如，通過UPDATE語句。

1. 3.

删除資料：從表中删除某一行時激活觸發程式，例如，通過DELETE和REPLACE語句。

trigger_event與以表操作方式激活觸發程式的SQL語句并不很類似，這點很重要。例如，關于INSERT的BEFORE觸發程式不僅能被INSERT語句激活，也能被LOAD DATA語句激活。

LOAD DATA INFILE語句簡述

LOAD DATA INFILE語句用于高速地從一個文本檔案中讀取行，并裝入一個表中。檔案名稱必須為一個文字字元串。

其文法為：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]
           

如果使用LOW_PRIORITY，則LOAD DATA語句的執行被延遲，直到沒有其它的用戶端從表中讀取為止。

如果一個MyISAM表滿足同時插入的條件（即該表在中間有空閑塊），并且對這個MyISAM表指定了CONCURRENT(适用于多線程共享情況)，則當LOAD DATA正在執行時，其它線程會從表中重新擷取資料。即使沒有其它線程在同時使用本表格，使用本選項也會略微影響LOAD DATA的性能。

如果指定了LOCAL，則被認為與連接配接的用戶端有關：

• 如果指定了LOCAL，則檔案會被客戶主機上的用戶端讀取，并被發送到伺服器。檔案會被給予一個完整的路徑名稱，以指定确切的位置。如果給定的是一個相對的路徑名稱，則此名稱會被了解為相對于啟動用戶端時所在的目錄。
• 如果LOCAL沒有被指定，則檔案必須位于伺服器主機上，并且被伺服器直接讀取。

當在伺服器主機上為檔案定位時，伺服器使用以下規則：

• 如果給定了一個絕對的路徑名稱，則伺服器使用此路徑名稱。
• 如果給定了帶有一個或多個引導元件的相對路徑名稱，則伺服器會搜尋相對于伺服器資料目錄的檔案。
• 如果給定了一個不帶引導元件的檔案名稱，則伺服器會在預設資料庫的資料庫目錄中尋找檔案。

注意，這些規則意味着名為./myfile.txt的檔案會從伺服器資料目錄中被讀取，而名為myfile.txt的同樣的檔案會從預設資料庫的資料庫目錄中讀取。

如果指定了REPLACE，則輸入行會替換原有行（對一個主索引或唯一索引具有相同值的行）。

如果指定IGNORE，則把原有行複制到唯一關鍵字值的輸入行被跳過。如果這兩個選項都不指定，則運作情況根據LOCAL關鍵詞是否被指定而定。不使用LOCAL時，當出現重複關鍵字值時，會發生錯誤，并且剩下的文本檔案被忽略。使用LOCAL時，預設的運作情況和IGNORE被指定時的情況相同；這是因為在運作中間，伺服器沒有辦法中止檔案的傳輸。

LOAD DATA INFILE是SELECT…INTO OUTFILE的補語。要從一個表中把資料寫入一個檔案中，應使用的是SELECT…INTO OUTFILE語句。要讀取檔案，放回到表中，應使用LOAD DATA INFILE。

FIELDS和LINES子句的文法對于兩個語句是一樣的。兩個子句都是自選的，但是如果兩個都被指定了，FIELDS必須位于LINES的前面。

如果不指定FIELDS子句，則預設值為如下語句的值：

如果不指定LINES子句，則預設值為如下語句的值：

即，當讀取輸入值時，預設值會使LOAD DATA INFILE按如下方式運作：

1. 在新行處尋找行的邊界。
2. 不會跳過任何行字首。
3. 在制表符處把行分解為字段。
4. 不希望字段被包含在任何引号字元之中。
5. 出現制表符、新行、或在‘\’前有‘\’時，了解為作為字段值一部分的文字字元。

相反的，當輸出值時，預設值會使SELECT…INTO OUTFILE按如下方式運作：

1. 在字段之間寫入制表符tab。
2. 不把字段包含在任何引号字元中。
3. 當字段值中出現制表符、新行或‘\’時，使用‘\’進行轉義。
4. 在行的末端寫入新行。

要寫入FIELDS ESCAPED BY ‘\’，您必須為待讀取的值指定兩個反斜杠，作為一個單反斜杠使用。

如果已經在Windows系統中生成了文本檔案，可能必須使用LINES TERMINATED BY ‘\r\n’來正确地讀取檔案，因為Windows程式通常使用兩個字元作為一個行終止符。

IGNORE number LINES選項可以被用于在檔案的開始處忽略行。例如，您可以使用IGNORE 1 LINES來跳過一個包含列名稱的起始标題行，即跳過第一行。

例：

首先，我在資料庫中建立了一個loadtest表

在D盤下的data.txt檔案中的資料為

在windows作業系統中，一開始加載的時候沒有添加LINES字段，預設換行符為’\n’，此時讀入資料會出現錯誤

添加LINES字段指定換行符為’\r\n’後，可以正确将資料載入

現在繼續看trigger的文法。

對于具有相同觸發程式動作時間和事件的給定表，不能有兩個觸發程式。例如，對于某一表，不能有兩個BEFORE UPDATE觸發程式。但可以有1個BEFORE UPDATE觸發程式和1個BEFORE INSERT觸發程式，或1個BEFORE UPDATE觸發程式和1個AFTER UPDATE觸發程式。

trigger_stmt是當觸發程式激活時執行的語句。如果你打算執行多個語句，可使用BEGIN … END複合語句結構。

測試

先用建立四個表：

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 
);
           

然後定義觸發程式，該觸發程式解讀為

- 在向test1表插入資料前觸發testref程式

- 向test2插入資料

- 删除test3中a3與插入test1的新資料相同的那一行資料

- 更新test4中的資料

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW 
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 +  WHERE a4 = NEW.a1;
  END
|

DELIMITER ;
           

使用别名OLD和NEW，能夠引用與觸發程式相關的表中的列。OLD.col_name在更新或删除它之前，引用已有行中的1列。NEW.col_name在更新它之後引用将要插入的新行的1列或已有行的1列。

激活觸發程式時，對于觸發程式引用的所有OLD和NEW列，需要具有SELECT權限，對于作為SET指派目标的所有NEW列，需要具有UPDATE權限。

接下來先向test3與test4表中插入一些預資料

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (), (), (), (), (), (), (), (), (), ();
           

然後開始向test1插入資料

INSERT INTO test1 VALUES 
    (), (), (), (), (), (), (), ();
           

最後可查得各個表中的資料如下：

DROP TRIGGER文法

DROP TRIGGER文法

舍棄觸發程式。方案名稱（schema_name）是可選的。如果省略了schema（方案），将從目前方案中舍棄觸發程式。

執行觸發程式過程中的錯誤處理方式

在觸發程式的執行過程中，MySQL處理錯誤的方式如下：

• 如果BEFORE觸發程式失敗，不執行相應行上的操作。
• 僅當BEFORE觸發程式（如果有的話）和行操作均已成功執行，才執行AFTER觸發程式。
• 如果在BEFORE或AFTER觸發程式的執行過程中出現錯誤，将導緻調用觸發程式的整個語句的失敗。
• 對于事務性表，如果觸發程式失敗（以及由此導緻的整個語句的失敗），該語句所執行的所有更改将復原。對于非事務性表，不能執行這類復原，因而，即使語句失敗，失敗之前所作的任何更改依然有效。