MySQL學習筆記—觸發程式

標籤：

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.

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

2.

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

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子句，則預設值為如下語句的值：

FIELDS TERMINATED BY ‘\t‘ ENCLOSED BY ‘‘ ESCAPED BY ‘\\‘

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

LINES TERMINATED BY ‘\n‘ STARTING BY ‘‘

即，當讀取輸入值時，預設值會使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 0);

然後定義觸發程式，該觸發程式解讀為
- 在向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 + 1 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   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

然後開始向test1插入資料

INSERT INTO test1 VALUES     (1), (3), (1), (7), (1), (8), (4), (4);

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

DROP TRIGGER文法

DROP TRIGGER文法

DROP TRIGGER [schema_name.]trigger_name

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

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

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

• 如果BEFORE觸發程式失敗，不執行相應行上的操作。

• 僅當BEFORE觸發程式（如果有的話）和行操作均已成功執行，才執行AFTER觸發程式。

• 如果在BEFORE或AFTER觸發程式的執行過程中出現錯誤，將導致調用觸發程式的整個語句的失敗。

• 對於事務性表，如果觸發程式失敗（以及由此導致的整個語句的失敗），該語句所執行的所有更改將復原。對於非事務性表，不能執行這類復原，因而，即使語句失敗，失敗之前所作的任何更改依然有效。

MySQL學習筆記—觸發程式