MariaDB / MySQL – 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).

SQL 語法:

DELIMITER // 
CREATE TRIGGER t1
AFTER UPDATE
ON testdb.merchandise
FOR EACH ROW
BEGIN
INSERT INTO testdb.salesrecord (itemname , name) VALUES (old.itemname,new.remark);
END //
DELIMITER ;

執行結果:

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 資料.

Trigger 權限

前面使用 root 登入,預設具有建立 Trigger 的權限,當建立時其 DEFINER 就是該使用者 root@localhost

現在使用另外一個使用者 user1 登入,會發現他無法建立 Trigger.

[root@localhost ~]# mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.64-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)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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 // 
ERROR 1142 (42000): TRIGGER command denied to user 'user1'@'localhost' for table 'merchandise'
MariaDB [testdb]>

我們只需要給該使用者 Trigger 的權限即可(具有建立,修改,刪除 Trigger 的權限).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-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)]> GRANT TRIGGER ON testdb.* TO user1@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT, TRIGGER ON `testdb`.* TO 'user1'@'localhost'                                                   |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

回到剛剛 user1 使用者(需重新登出,登入).即可建立 Trigger 了.

MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.64-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)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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.00 sec)

MariaDB [testdb]> DELIMITER ;

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: user1@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

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

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

新增 Trigger

測試 Trigger

移除 Trigger

錯誤訊息

Trigger 權限

發佈留言 取消回覆

發佈留言取消回覆