SQL – Trigger

測試環境為 CentOS 7 x86_64 (虛擬機)

範例: 兩個 Table 表單用來記錄 1.貨物品項及其數量, 2.銷售員及其銷售貨物, 當表單 1 有更新時,須自動更新 (使用 Trigger) 到表單 2.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> use testdb;
Database changed

表單 1.貨物品項及其數量.

MariaDB [testdb]> CREATE TABLE merchandise (K1 int(11) NOT NULL auto_increment , itemname varchar(199) NOT NULL, inrecord int(11) NOT NULL , outrecord int(11) NOT NULL ,remark varchar(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> DESCRIBE merchandise;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| K1        | int(11)      | NO   | PRI | NULL    | auto_increment |
| itemname  | varchar(199) | NO   |     | NULL    |                |
| inrecord  | int(11)      | NO   |     | NULL    |                |
| outrecord | int(11)      | NO   |     | NULL    |                |
| remark    | varchar(199) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

表單 2.銷售員及其銷售貨物.

MariaDB [testdb]> CREATE TABLE salesrecord (K1 int(11) NOT NULL auto_increment , itemname varchar(199) NOT NULL, name varchar(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> DESCRIBE salesrecord;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| K1       | int(11)      | NO   | PRI | NULL    | auto_increment |
| itemname | varchar(199) | NO   |     | NULL    |                |
| name     | varchar(199) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

新增 Trigger

Trigger 語法如下:

MariaDB [testdb]> help create trigger
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

參數說明:

  • trigger_time: BEFORE | AFTER , Trigger 執行的時機是在 INSERT , UPDATE , DELETE 指令 之前 BEFORE 或之後 AFTER.
  • trigger_event: INSERT | UPDATE | DELETE , 只有這幾種指令才能設定觸發 TRIGGER 機制.
  • trigger_body: 可以是單一 SQL指令,多指令時可以使用 BEGIN … END.

Trigger 規則: 當表單 1 (testdb.merchandise) 有更新 (UPDATE) 時,須自動更新到表單 2(testdb.salesrecord).

MariaDB [testdb]> DELIMITER // 
MariaDB [testdb]> CREATE TRIGGER t1
    -> AFTER UPDATE
    -> ON testdb.merchandise
    -> FOR EACH ROW 
    -> BEGIN
    -> INSERT INTO testdb.salesrecord (itemname , name) VALUES (old.itemname,new.remark);
    -> END // 
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> DELIMITER ;
  • DELIMITER // //
    mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
  • DELIMITER ;
    把結束符號修改回為 分號 “;” .
  • BEGIN END
    SELECT 語法宣告必須包含在裡面.
  • 欄位關鍵字 (OLD與NEW)
    INSERT , UPDATE , DELETE 觸發 TRIGGER 時,資料可能已經更新,我們可以使用 OLD 與 NEW 這兩個關鍵字來決定資料欄位要使用的是新資料或是舊資料.不過依據不同指令 (INSERT , UPDATE , DELETE) 會有以下的限制.

    Trigger OLD(舊資料) NEW(新資料)
    INSERT 無法使用 可使用
    UPDATE 可使用 可使用
    DELETE 可使用 無法使用

測試 Trigger

INSERT 一筆新的銷售物件到 表單 1 (testdb.merchandise) 不會 Trigger .

MariaDB [testdb]> INSERT INTO testdb.merchandise (itemname ,inrecord ,outrecord ,remark) VALUES ('Apple99', '100', '0', 'Ben');

MariaDB [testdb]> SELECT * FROM merchandise;
+----+----------+----------+-----------+--------+
| K1 | itemname | inrecord | outrecord | remark |
+----+----------+----------+-----------+--------+
|  1 | Apple99  |      100 |         0 | Ben    |
+----+----------+----------+-----------+--------+
1 row in set (0.01 sec)

MariaDB [testdb]> SELECT * FROM salesrecord;
Empty set (0.00 sec)

使用 UPDATE 時更新 表單 1 (testdb.merchandise) 時,則自動更新到表單 2(testdb.salesrecord),資料來源為 testdb.merchandise (old.itemname,new.remark) .

MariaDB [testdb]> UPDATE testdb.merchandise SET inrecord = '98',outrecord = '2' WHERE merchandise.K1 =1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM merchandise;
+----+----------+----------+-----------+--------+
| K1 | itemname | inrecord | outrecord | remark |
+----+----------+----------+-----------+--------+
|  1 | Apple99  |       98 |         2 | Ben    |
+----+----------+----------+-----------+--------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM salesrecord;
+----+----------+------+
| K1 | itemname | name |
+----+----------+------+
|  1 | Apple99  | Ben  |
+----+----------+------+
1 row in set (0.00 sec)

移除 Trigger

MariaDB [testdb]> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: t1
               Event: UPDATE
               Table: merchandise
           Statement: BEGIN
INSERT INTO testdb.salesrecord (itemname , name) VALUES (old.itemname,new.remark);
END
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
MariaDB [testdb]> DROP TRIGGER t1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> SHOW TRIGGERS\G
Empty set (0.01 sec)

錯誤訊息

Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

如下 Trigger 的範例,新增資料 INSERT 時更新 UPDATE 相同 Table 的一個欄位.

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE TRIGGER test
    -> AFTER INSERT 
    -> ON testtable 
    -> FOR EACH ROW 
    -> BEGIN
    -> UPDATE testtable SET status='Test Completed';
    -> END // 
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DELIMITER ;

MariaDB [testdb]> insert into testtable (loops , testloops) value(1,100);
ERROR 1442 (HY000): Can't update table 'machine' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

當要新增資料時就會顯示如上的錯誤訊息,原因在於執行 INSERT 時會鎖定欄位,這時候 INSERT 觸發器觸發時自然無法更改 Table 資料.

沒有解決問題,試試搜尋本站其他內容

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料