mysql觸發器old實例（mysql觸發器old和new）

本文目錄一覽：

• 1、關於mysql中觸發器old和new如何更好的區別我有話要說
• 2、數據庫的觸發器怎麼進行高級操作？
• 3、計算機mysql觸發器中創建觸發器裏面監視的table和下面的new或者是old指代的列有什麼關係？

關於mysql中觸發器old和new如何更好的區別我有話要說

Mysql的觸發器相當於內部處理的一些過程，不帶入和帶出任何的參數。

其內部使用的參數就是新舊兩條記錄old和new的字段。

用於完成數據表之間的觸發操作，來保證數據庫的一致性、完整性。

Mysql的存儲過程是類似於其它編程語言中的函數的功能。

存儲過程內部可以使用順序循環和轉移三種基本程序結構，而且整個存儲過程可以接受和返回參數。

數據庫的觸發器怎麼進行高級操作？

1.new與old在數據庫的觸發器中經常會用到更新前的值和更新後的值，所以掌握new和old的語法很重要。

old：表示操作執行前的數據行。

new：表示操作執行後的數據行。

一User數據表如圖User數據表所示，若執行以下更新操作語句：updateUsersetscore=80whereuser_id=1

User數據表

則在此操作中，old表示未執行update語句前user_id=1這行記錄；而new表示執行update語句後user_id=1這行記錄。

從上面的表述中可知，new與old均表示某一行記錄，old所表示的是寫操作發生前的這一行舊數據，new則表示寫操作發生後的這一行新的數據。正因如此，可以把new與old看作面向對象編程裏面的一個對象或實例，與面向對象的方式類似，可用new.字段名或old.字段名的方式進行存取值。

old.字段名：表示未執行操作前的該行對應的某字段值。

new.字段名：表示執行操作後的該行對應的某字段值。

在上面User表的update操作中：old.score=60，表示update操作前score字段的舊值是60。

new.score=80，表示update操作後score字段的新值是80。

如果要使用new語句進行賦值，只能在before類型的觸發器中使用，不能在after類型的觸發器中使用。

更新操作前使用before先賦值，再插入數據庫中。如以下語句是正確的：CREATETRIGGERupdatepriceBEFOREinsertONconsumeinfoFOREACHROWBEGINsetnew.金額=0;END更新操作後，不能在after中用new賦值，因為操作已經結束，只能讀取內容。如以下語句是錯誤的：CEATETRIGGERupdatepriceAFTERinsertONconsumeinfoFOREACHROWBEGINsetnew.金額=0;ENDnew與old的區別：前者可在before觸發器中賦值、取值，也可在after觸發器中取值；

後者只能用於取值，因為賦值沒有意義。

注意：INSERT語句，只有new合法；

DELETE語句，只有old才合法；

UPDATE語句，可以同時使用new和old。

2.before與afterbefore與after表示觸發器觸發的時間點是在寫操作開始之前，還是在寫操作完成後，正因為它們有時間點先後的問題，因此它們的功能與使用場合有非常大的差別。

（1)before（（1)先完成觸發操作，再執行業務數據的增刪改。

（2）觸發的語句先於監視的業務語句。

（3）有機會影響即將發生的操作。

2）after（（1)先完成業務數據的增刪改，再觸發。

（2）觸發的語句晚於監視的業務語句。

（3）無法影響前面的增刪改動作。

3.異常處理MySQL現有版本中不支持自定義異常，當某處需要拋出異常時，可拋出一個系統異常（類似運行異常）。如故意往不存在的表中插入數據等方式來觸發系統異常的拋出，當異常拋出時，本次正在執行的所有操作會終止執行，並回滾所有數據到操作發生前的狀態。

在觸發器中，需要拋出異常的場景有很多，以下兩個方面較為普遍：（（1)新進來的數據不符合業務邏輯。

例如，①倉庫最大商品庫存數為10，訂單要求一次性購買數量20。

②銀行賬戶上只有50元，想要支付100元的賬單。

（2）權限不足，不允許操作。

例如，①普通用戶通過非法途徑操作核心資源表。

②在非工作日修改業務數據。4.觸發器案例用mytab.sql腳本創建表環境，然後用下面語句創建觸發器。當往mytab表添加記錄時，觸發器中將拋出異常，導致所有操作終止，並回滾所有操作的數據。

DELIMITER//DROPTRIGGERIFEXISTSt_exception_trigger;CREATETRIGGERt_exception_triggerAFTERINSERTONmytabFOREACHROWBEGIN–tab3表不存在

計算機mysql觸發器中創建觸發器裏面監視的table和下面的new或者是old指代的列有什麼關係？

建立兩個單域的表格。一個表格中為姓名列表(表格名：data)。

另一個表格中是所插入字符的字符數(表格名：chars)。在data表格中定義一個觸發器。

每次在其中插入一個新姓名時，chars表格中運行的總數就會根據新插入記錄的字符數目進行自動更新。

(見列表A)

mysql CREATE TABLE data (name VARCHAR(255));

Query OK, 0 rows affected (0.09 sec)

mysql CREATE TABLE chars (count INT(10));

Query OK, 0 rows affected (0.07 sec)

mysql INSERT INTO chars (count) VALUES (0);

Query OK, 1 row affected (0.00 sec)

mysql CREATE TRIGGER t1 AFTER INSERT ON

data FOR EACH ROW UPDATE chars SET count = count + CHAR_LENGTH(NEW.name);

Query OK, 0 rows affected (0.01 sec)

列表A

理解上面代碼的關鍵在於CREATE TRIGGER命令，被用來定義一個新觸發器。這個命令建立一個新觸發器，假定的名稱為t1，每次有一個新記錄插入到data表格中時，t1就被激活。

在這個觸發器中有兩個重要的子句：

AFTER INSERT子句表明觸發器在新記錄插入data表格後激活。

UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)子句表示觸發器激活後執行的SQL命令。在本例中，該命令表明用新插入的data.name域的字符數來更新 chars.count欄。這一信息可通過內置的MySQL函數CHAR_LENGTH()獲得。

放在源表格域名前面的NEW關鍵字也值得注意。這個關鍵字表明觸發器應考慮域的new值(也就是說，剛被插入到域中的值)。MySQL還支持相應的OLD前綴，可用它來指域以前的值。

可以通過調用SHOW TRIGGER命令來檢查觸發器是否被激活，如列表B所示。

mysql SHOW TRIGGERS\G

*************************** 1. row ***************************

?Trigger: t1

?Event: INSERT

?Table: data

Statement: UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)

Timing: AFTER

?Created: NULL

ql_mode:

1 row in set (0.01 sec)

列表B

激活觸發器後，開始對它進行測試。試着在data表格中插入幾個記錄：

mysql INSERT INTO data (name) VALUES (‘Sue’), (‘Jane’);

Query OK, 2 rows affected (0.00 sec)

Records: 2?Duplicates: 0?Warnings: 0

然後檢查chars表格看觸發器是否完成它該完成的任務：

mysql SELECT * FROM chars;

+——-+

| count |

+——-+

| 7|

+——-+

1 row in set (0.00 sec)

data表格中的INSERT命令激活觸發器，計算插入記錄的字符數，並將結果存儲在chars表格中。如果往data表格中增加另外的記錄，chars.count值也會相應增加。

觸發器應用完畢後，可有DROP TRIGGER命令輕鬆刪除它。

mysql DROP TRIGGER t1;

Query OK, 0 rows affected (0.00 sec)

注意：理想情況下，你還需要一個倒轉觸發器，每當一個記錄從源表格中刪除時，它從字符總數中減去記錄的字符數。這很容易做到，你可以把它當作練習來完成。提示：應用BEFORE DELETE ON子句是其中一種方法。

現在，要建立一個審計記錄來追蹤對這個表格所做的改變。這個記錄將反映表格的每項改變，並向用戶說明由誰做出改變以及改變的時間。需要建立一個新表格來存儲這一信息(表格名：audit)，如下所示。(列表C)

mysql CREATE TABLE audit (id INT(7), balance FLOAT, user VARCHAR(50)

NOT NULL, time TIMESTAMP NOT NULL);

Query OK, 0 rows affected (0.09 sec)

列表C

接下來，我將在accounts表格中定義一個觸發器。(列表D)

mysql CREATE TRIGGER t1 AFTER UPDATEON accounts

FOR EACH ROW INSERT INTO audit (id, balance, user, time)

VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW());

Query OK, 0 rows affected (0.04 sec)

列表D

要是已經走到這一步，就很容易理解。accounts表格每經歷一次UPDATE，觸發器插入(INSERT)對應記錄的id、新的餘額、當前時間和登錄audit表格的用戶的名稱。

實現中的例子：用觸發器審計記錄

既然了觸發器的基本原理，來看一個稍稍複雜的例子。常用觸發器來建立一個自動「審計記錄」，以記錄各種用戶對數據庫的更改。為了解審計記錄的實際應用，請看下面的表格(表格名：accounts)，它列出了一個用戶的三個銀行賬戶餘額。(表A)

mysql SELECT * FROM accounts;

+—-+————+———+

| id | label| balance |

+—-+————+———+

|1 | Savings #1 |500 |

|2 | Current #1 |2000 |

|3 | Current #2 |3500 |

+—-+————+———+

3 rows in set (0.00 sec)

表A

然後，檢查觸發器是否被激活：

mysql SHOW TRIGGERS \G

*************************** 1. row ***************************

?Trigger: t1

?Event: UPDATE

?Table: accounts

Statement: INSERT INTO audit (id, balance, user, time)

VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW())

Timing: AFTER

?Created: NULL

Sql_mode:

1 row in set (0.01 sec)

再來看最後的結果(列表E)：

mysql UPDATE accounts SET balance = 500 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1?Changed: 1?Warnings: 0

mysql UPDATE accounts SET balance = 900 WHERE id = 3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1?Changed: 1?Warnings: 0

mysql UPDATE accounts SET balance = 1900 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1?Changed: 1?Warnings: 0

列表E

注意，對accounts表格所作的改變已被記錄到audit表格中，將來如果出現問題，可以方便地從中進行恢復。

mysql SELECT * FROM audit;

+——+———+—————-+———————+

| id| balance | user| time|

+——+———+—————-+———————+

|1 |500 | root@localhost | 2006-04-22 12:52:15 |

|3 |900 | root@localhost | 2006-04-22 12:53:15 |

|1 |1900 | root@localhost | 2006-04-22 12:53:23 |

+——+———+—————-+———————+

3 rows in set (0.00 sec)