實驗七 函數與觸發器

来源:https://www.cnblogs.com/moeyur/archive/2023/05/09/17386509.html
-Advertisement-
Play Games

#實驗七 函數與觸發器 第1關：定義、調用參數函數 相關知識 MySQL存儲函數 存儲函數和存儲過程一樣，都是在資料庫中定義一些 SQL 語句的集合。存儲函數可以通過 return 語句返回函數值，主要用於計算並返回一個值。而存儲過程沒有直接返回值，主要用於執行操作。 在 MySQL 中，使用 CR ...


實驗七 函數與觸發器

第1關：定義、調用參數函數

相關知識

MySQL存儲函數

存儲函數和存儲過程一樣，都是在資料庫中定義一些 SQL 語句的集合。存儲函數可以通過 return 語句返回函數值，主要用於計算並返回一個值。而存儲過程沒有直接返回值，主要用於執行操作。

在 MySQL 中，使用 CREATE FUNCTION 語句來創建存儲函數，其語法形式如下：

  1. CREATE FUNCTION sp_name ([func_parameter[...]])
  2. RETURNS type
  3. [characteristic ...] routine_body

其中：

  • sp_name 參數：表示存儲函數的名稱；
  • func_parameter：表示存儲函數的參數列表；
  • RETURNS type：指定返回值的類型；
  • characteristic 參數：指定存儲函數的特性，該參數的取值與存儲過程是一樣的；
  • routine_body 參數：表示 SQL 代碼的內容，可以用 BEGIN...END 來標示 SQL 代碼的開始和結束。

func_parameter 可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下： [IN | OUT | INOUT] param_name type;

其中：

  • IN 表示輸入參數，OUT 表示輸出參數，INOUT 表示既可以輸入也可以輸出；
  • param_name 參數是存儲函數的參數名稱；
  • type 參數指定存儲函數的參數類型，該類型可以是 MySQL 資料庫的任意數據類型。

例如：使用 CREATE FUNCTION 創建查詢 tb_student 表中某個學生姓名的函數，SQL 語句和執行過程如下：

  1. mysql> USE test;
  2. Database changed
  3. mysql> DELIMITER //
  4. mysql> CREATE FUNCTION func_student(id INT(11))
  5. -> RETURNS VARCHAR(20)
  6. -> COMMENT '查詢某個學生的姓名'
  7. ->BEGIN
  8. -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
  9. ->END//
  10. Query OK,0 rows affected (0.10 sec)
  11. mysql> DELIMITER ;

上述代碼中，創建了 func_student 函數，該函數擁有一個類型為 INT(11) 的參數 id，返回值為 VARCHAR(20) 類型。SELECT 語句從 tb_student 表中查詢 id 欄位值等於所傳入參數 id 值的記錄，同時返回該條記錄的 name 欄位值。

創建函數與創建存儲過程一樣，需要通過命令 DELIMITER //將 SQL 語句的結束符由";"修改為"//"，最後通過命令 DELIMITER ;將結束符號修改成 SQL 語句中預設的結束符號。

編程要求

根據提示，在右側編輯器補充代碼。 定義、調用參數函數（func_sqty）：查詢返回指定供應商的供應零件總數量。

測試說明

平臺會對你編寫的代碼進行測試：

測試輸入：無； 預期輸出：

  1. func_sqty('S1')
  2. 1100
  3. func_sqty('S2')
  4. 2000
  5. func_sqty('S3')
  6. 400
use demo;

#代碼開始

#定義、調用參數函數（func\_sqty）：查詢返回指定供應商的供應零件總數量。

DELIMITER $$

CREATEFUNCTION func\_sqty(p\_sno CHAR(2))

RETURNS INT

BEGIN

    DECLARE v\_sqty INT;

    SELECTSUM(qty)INTO v\_sqty FROM spj WHERE sno = p\_sno;

    RETURN v\_sqty;

END$$

DELIMITER ;

#代碼結束

select func\_sqty('S1');

select func\_sqty('S2');

select func\_sqty('S3');

第2關：觸發器應用（1）

相關知識

MySQL觸發器介紹

觸發器（trigger）是與表/庫上的操作事件相關的一類特殊的存儲過程，它在相關事件發生時被觸發執行，常用於實現數據的完整性約束和業務規則。

MySQL 的觸發器和存儲過程一樣，都是嵌入到 MySQL 中的一段程式，是 MySQL 中管理數據的有力工具。不同的是執行存儲過程要使用 CALL 語句來調用，而觸發器的執行不需要使用 CALL 語句來調用，也不需要手工啟動，而是通過對數據表的相關操作來觸發、激活從而實現執行。比如當對 student 表進行操作（INSERT，DELETE 或 UPDATE）時就會激活它執行。

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性約束，它能夠對資料庫中的表做級聯修改，提供比CHECK約束更複雜的的數據完整性約束，並自定義錯誤消息。觸發器的主要作用有以下幾個方面：

  1. 強制資料庫間的引用完整性
  2. 級聯修改資料庫中所有相關的表，自動觸發其它與之相關的操作
  3. 跟蹤變化，撤銷或回滾違法操作，防止非法修改數據
  4. 返回自定義的錯誤消息，約束無法返回信息，而觸發器可以
  5. 觸發器可以調用更多的存儲過程

MySQL創建觸發器

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 語句創建觸發器。

語法格式如下：

  1. CREATE <觸發器名>< BEFORE | AFTER >
  2. <INSERT | UPDATE | DELETE >
  3. ON <表名> FOR EACH Row
  4. <觸發器主體>

語法說明如下。 (1) 觸發器名 觸發器的名稱，觸發器在當前資料庫中必須具有唯一的名稱。如果要在某個特定資料庫中創建，名稱前面應該加上資料庫的名稱。 (2) INSERT | UPDATE | DELETE觸發事件，用於指定激活觸發器的語句的種類。 註意：三種觸發器的執行時間如下。 INSERT：將新行插入表時激活觸發器。 DELETE： 從表中刪除某一行數據時激活觸發器。 UPDATE：更改表中某一行數據時激活觸發器。 (3) BEFORE | AFTER BEFORE 和 AFTER，觸發器被觸發的時刻，表示觸發器是在激活它的語句之前或之後觸發。若希望驗證新數據是否滿足條件，則使用 BEFORE 選項；若希望在激活觸發器的語句執行之後完成幾個或更多的改變，則通常使用 AFTER 選項。 (4) 表名 與觸發器相關聯的表名，此表必須是永久性表，不能將觸發器與臨時表或視圖關聯起來。 (5) 觸發器主體 觸發器動作主體，包含觸發器激活時將要執行的 MySQL 語句。如果要執行多個語句，可使用 BEGIN…END 複合語句結構。 在觸發器主體中，我們可以使用NEW表示將要插入的新行，OLD表示將要刪除的舊行。通過OLD，NEW獲取它們的欄位內容，方便在觸發操作中使用，下麵是對應事件是否支持OLD、NEW的對應關係：

事件 OLD NEW
INSERT × √
--- --- ---
DELETE √ ×
UPDATE √ √

由於UPDATE相當於刪除舊行（OLD），然後插入新行（NEW），所以UPDATE同時支持OLD、NEW。 (6) FOR EACH ROW一般是指行級觸發，對於受觸發事件影響的每一行都要激活觸發器的動作。例如，使用 INSERT 語句向某個表中插入多行數據時，觸發器會對每一行數據的插入都執行相應的觸發器動作。

MySQL分隔符（DELIMITER）

MySQL預設使用";"作為分隔符，SQL語句遇到";"就會提交。而我們的觸發器中可能會有多個";"符，為了防止觸發器創建語句過早的提交，我們需要臨時修改MySQL分隔符，創建完後，再將分隔符改回來。使用DELIMITER可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--觸發器創建語句；
  3. $ --提交創建語句；
  4. DELIMITER ;

MySQL刪除觸發器

  1. 可以使用drop trigger刪除觸發器： drop trigger trigger_name;
  2. 刪除前先判斷觸發器是否存在： drop trigger if exists trigger_name

MySQL查看觸發器

另外，在 MySQL 中，若需要查看資料庫中已有的觸發器，則可以使用 SHOW TRIGGERS 語句。 語法格式如下： SHOW TRIGGERS;

在 MySQL 中，所有觸發器的信息都存在 information_schema資料庫的 triggers表中，可以通過查詢命令 SELECT來查看，具體的語法如下： SELECT * FROM information_schema.triggers WHERE trigger_name= '觸發器名';

其中，'觸發器名'用來指定要查看的觸發器的名稱，需要用單引號引起來。這種方式可以查詢指定的觸發器，使用起來更加方便、靈活。

MySQL觸發器的使用

1** 、 MySQL 觸發器 Insert 觸發更新同一張表：** 　　下麵我們有一個表"tmp1"，tmp1表有兩個整型欄位：n1、n2。我們要通過觸發器實現，在tmp1插入記錄時，自動將n2欄位的值設置為n1欄位的5倍。 　創建測試表和觸發器：

  1. --創建測試表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --創建觸發器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

測試觸發更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

２、** MySQL 觸發器 Update **觸發更新另一張表： 　　下麵有有兩個表tmp1、tmp2，兩個表都有一個相同的欄位name。使用觸發器實現更新一個表的name時，將另外一個表的name也更新。 　創建測試表和觸發器：

  1. --創建測試表和插入測試數據
  2. drop table if exists tmp1;
  3. drop table if exists tmp2;
  4. create table tmp1 (id int, name varchar(128))default charset='utf8';
  5. create table tmp2 (fid int, name varchar(128))default charset='utf8';
  6. insert into tmp1 values(1,'愛E族');
  7. insert into tmp2 values(1,'愛E族');
  9. --創建觸發器
  10. drop trigger if exists tmp1_update;
  11. create trigger tmp1_update
  12. after update on tmp1
  13. for each row
  14. begin
  15. update tmp2 set name=new.name where fid=new.id;
  16. end;

測試觸發更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|愛E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|愛E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 1 row inset(0.00 sec)

編程要求

定義一個觸發器（tr_spj_insert），完成向SPJ表新增數據時，及時更新所有供應商供應零件總數量。 根據提示，在右側編輯器補充代碼。

原始表結構及數據如下所示：

sno sname status city sqty
S1 精益 20 天津
--- --- --- --- ---
S2 盛錫 10 北京
S3 東方紅 30 北京
S4 豐泰盛 20 天津
S5 為民 30 上海
pno pname color weight
P1 螺母 紅 12
--- --- --- ---
P2 螺栓 綠 17
P3 螺絲刀 藍 14
P4 螺絲刀 紅 14
P5 凸輪 藍 40
P6 齒輪 紅 30
jno jname city
J1 三建 北京
--- --- ---
J2 一汽 長春
J3 彈簧廠 天津
J4 造船廠 天津
J5 機車廠 唐山
J6 無線電廠 常州
J7 半導體廠 南京

測試說明

平臺會對你編寫的代碼進行測試：

預期輸出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_insert AFTER INSERT spj
  3. sno sname status city sqty
  4. S1 精益20天津1100
  5. S2 盛錫10北京2000
  6. S3 東方紅30北京400
  7. S4 豐泰盛20天津600
  8. S5 為民30上海1000
use demo;

#定義一個觸發器（tr\_spj\_insert），完成向SPJ表新增數據時，及時更新所有供應商供應零件總數量。

#代碼開始

DELIMITER $$

CREATETRIGGER tr\_spj\_insert AFTER INSERTON spj

FOREACHROW

BEGIN

    UPDATE s SET sqty =(SELECTSUM(qty)FROM spj WHERE sno = NEW.sno)WHERE sno = NEW.sno;

END$$

DELIMITER ;

#代碼結束

#以下代碼不要改動或刪除，將會對創建的觸發器進行測試

insertinto spj values

('S1','P1','J1',200),

('S1','P1','J3',100),

('S1','P1','J4',700),

('S1','P2','J2',100),

('S2','P3','J1',400),

('S2','P3','J2',200),

('S2','P3','J4',500),

('S2','P3','J5',400),

('S2','P5','J1',400),

('S2','P5','J2',100),

('S3','P1','J1',200),

('S3','P3','J1',200),

('S4','P5','J1',100),

('S4','P6','J3',300),

('S4','P6','J4',200),

('S5','P2','J4',100),

('S5','P3','J1',200),

('S5','P6','J2',200),

('S5','P6','J4',500);

第3關：觸發器應用（2）

相關知識

MySQL觸發器介紹

觸發器（trigger）是與表/庫上的操作事件相關的一類特殊的存儲過程，它在相關事件發生時被觸發執行，常用於實現數據的完整性約束和業務規則。

MySQL 的觸發器和存儲過程一樣，都是嵌入到 MySQL 中的一段程式，是 MySQL 中管理數據的有力工具。不同的是執行存儲過程要使用 CALL 語句來調用，而觸發器的執行不需要使用 CALL 語句來調用，也不需要手工啟動，而是通過對數據表的相關操作來觸發、激活從而實現執行。比如當對 student 表進行操作（INSERT，DELETE 或 UPDATE）時就會激活它執行。

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性約束，它能夠對資料庫中的表做級聯修改，提供比CHECK約束更複雜的的數據完整性約束，並自定義錯誤消息。觸發器的主要作用有以下幾個方面：

  1. 強制資料庫間的引用完整性
  2. 級聯修改資料庫中所有相關的表，自動觸發其它與之相關的操作
  3. 跟蹤變化，撤銷或回滾違法操作，防止非法修改數據
  4. 返回自定義的錯誤消息，約束無法返回信息，而觸發器可以
  5. 觸發器可以調用更多的存儲過程

MySQL創建觸發器

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 語句創建觸發器。

語法格式如下：

  1. CREATE <觸發器名>< BEFORE | AFTER >
  2. <INSERT | UPDATE | DELETE >
  3. ON <表名> FOR EACH Row
  4. <觸發器主體>

語法說明如下。 (1) 觸發器名 觸發器的名稱，觸發器在當前資料庫中必須具有唯一的名稱。如果要在某個特定資料庫中創建，名稱前面應該加上資料庫的名稱。 (2) INSERT | UPDATE | DELETE觸發事件，用於指定激活觸發器的語句的種類。 註意：三種觸發器的執行時間如下。 INSERT：將新行插入表時激活觸發器。 DELETE： 從表中刪除某一行數據時激活觸發器。 UPDATE：更改表中某一行數據時激活觸發器。 (3) BEFORE | AFTER BEFORE 和 AFTER，觸發器被觸發的時刻，表示觸發器是在激活它的語句之前或之後觸發。若希望驗證新數據是否滿足條件，則使用 BEFORE 選項；若希望在激活觸發器的語句執行之後完成幾個或更多的改變，則通常使用 AFTER 選項。 (4) 表名 與觸發器相關聯的表名，此表必須是永久性表，不能將觸發器與臨時表或視圖關聯起來。 (5) 觸發器主體 觸發器動作主體，包含觸發器激活時將要執行的 MySQL 語句。如果要執行多個語句，可使用 BEGIN…END 複合語句結構。 在觸發器主體中，我們可以使用NEW表示將要插入的新行，OLD表示將要刪除的舊行。通過OLD，NEW獲取它們的欄位內容，方便在觸發操作中使用，下麵是對應事件是否支持OLD、NEW的對應關係：

事件 OLD NEW
INSERT × √
--- --- ---
DELETE √ ×
UPDATE √ √

由於UPDATE相當於刪除舊行（OLD），然後插入新行（NEW），所以UPDATE同時支持OLD、NEW。 (6) FOR EACH ROW一般是指行級觸發，對於受觸發事件影響的每一行都要激活觸發器的動作。例如，使用 INSERT 語句向某個表中插入多行數據時，觸發器會對每一行數據的插入都執行相應的觸發器動作。

MySQL分隔符（DELIMITER）

MySQL預設使用";"作為分隔符，SQL語句遇到";"就會提交。而我們的觸發器中可能會有多個";"符，為了防止觸發器創建語句過早的提交，我們需要臨時修改MySQL分隔符，創建完後，再將分隔符改回來。使用DELIMITER可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--觸發器創建語句；
  3. $ --提交創建語句；
  4. DELIMITER ;

MySQL刪除觸發器

  1. 可以使用drop trigger刪除觸發器： drop trigger trigger_name;
  2. 刪除前先判斷觸發器是否存在： drop trigger if exists trigger_name

MySQL查看觸發器

另外，在 MySQL 中，若需要查看資料庫中已有的觸發器，則可以使用 SHOW TRIGGERS 語句。 語法格式如下： SHOW TRIGGERS;

在 MySQL 中，所有觸發器的信息都存在 information_schema資料庫的 triggers表中，可以通過查詢命令 SELECT來查看，具體的語法如下： SELECT * FROM information_schema.triggers WHERE trigger_name= '觸發器名';

其中，'觸發器名'用來指定要查看的觸發器的名稱，需要用單引號引起來。這種方式可以查詢指定的觸發器，使用起來更加方便、靈活。

MySQL觸發器的使用

1** 、 MySQL 觸發器 Insert 觸發更新同一張表：** 　　下麵我們有一個表"tmp1"，tmp1表有兩個整型欄位：n1、n2。我們要通過觸發器實現，在tmp1插入記錄時，自動將n2欄位的值設置為n1欄位的5倍。 　創建測試表和觸發器：

  1. --創建測試表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --創建觸發器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

測試觸發更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

２、** MySQL 觸發器 Update **觸發更新另一張表： 　　下麵有有兩個表tmp1、tmp2，兩個表都有一個相同的欄位name。使用觸發器實現更新一個表的name時，將另外一個表的name也更新。 　創建測試表和觸發器：

  1. --創建測試表和插入測試數據
  2. drop table if exists tmp1;
  3. drop table if exists tmp2;
  4. create table tmp1 (id int, name varchar(128))default charset='utf8';
  5. create table tmp2 (fid int, name varchar(128))default charset='utf8';
  6. insert into tmp1 values(1,'愛E族');
  7. insert into tmp2 values(1,'愛E族');
  9. --創建觸發器
  10. drop trigger if exists tmp1_update;
  11. create trigger tmp1_update
  12. after update on tmp1
  13. for each row
  14. begin
  15. update tmp2 set name=new.name where fid=new.id;
  16. end;

測試觸發更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|愛E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|愛E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 1 row inset(0.00 sec)

編程要求

定義一個觸發器（tr_spj_delete），完成向SPJ表刪除數據時，及時更新所有供應商供應零件總數量。 根據提示，在右側編輯器補充代碼。

原始表結構及數據如下所示：

sno sname status city sqty
S1 精益 20 天津
--- --- --- --- ---
S2 盛錫 10 北京
S3 東方紅 30 北京
S4 豐泰盛 20 天津
S5 為民 30 上海
pno pname color weight
P1 螺母 紅 12
--- --- --- ---
P2 螺栓 綠 17
P3 螺絲刀 藍 14
P4 螺絲刀 紅 14
P5 凸輪 藍 40
P6 齒輪 紅 30
jno jname city
J1 三建 北京
--- --- ---
J2 一汽 長春
J3 彈簧廠 天津
J4 造船廠 天津
J5 機車廠 唐山
J6 無線電廠 常州
J7 半導體廠 南京
sno pno jno qty
S1 P1 J1 200
--- --- --- ---
S1 P1 J3 100
S1 P1 J4 700
S1 P2 J2 100
S2 P3 J1 400
S2 P3 J2 200
S2 P3 J4 500
S2 P3 J5 400
S2 P5 J1 400
S2 P5 J2 100
S3 P1 J1 200
S3 P3 J1 200
S4 P5 J1 100
S4 P6 J3 300
S4 P6 J4 200
S5 P2 J4 100
S5 P3 J1 200
S5 P6 J2 200
S5 P6 J4 500

測試說明

平臺會對你編寫的代碼進行測試：

預期輸出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_delete AFTER DELETE spj
  3. sno sname status city sqty
  4. S1 精益20天津900
use demo;

#定義一個觸發器（tr\_spj\_delete），完成向SPJ表刪除數據時，及時更新所有供應商供應零件總數量。

#代碼開始

DELIMITER $$

CREATETRIGGER tr\_spj\_delete AFTER DELETEON spj FOREACHROW

BEGIN

    UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;

END$$

DELIMITER ;

UPDATE s

JOIN(

    SELECT sno,SUM(qty)AS total\_qty

    FROM spj

    GROUPBY sno

)AS t ON s.sno = t.sno

SET s.sqty = t.total\_qty;

#代碼結束

#以下代碼不要改動或刪除，將會對創建的觸發器進行測試

DELETEFROM spj WHERE sno='S1'AND pno='P1'AND jno='J1';

第4關：觸發器應用（3）

相關知識

MySQL觸發器介紹

觸發器（trigger）是與表/庫上的操作事件相關的一類特殊的存儲過程，它在相關事件發生時被觸發執行，常用於實現數據的完整性約束和業務規則。

MySQL 的觸發器和存儲過程一樣，都是嵌入到 MySQL 中的一段程式，是 MySQL 中管理數據的有力工具。不同的是執行存儲過程要使用 CALL 語句來調用，而觸發器的執行不需要使用 CALL 語句來調用，也不需要手工啟動，而是通過對數據表的相關操作來觸發、激活從而實現執行。比如當對 student 表進行操作（INSERT，DELETE 或 UPDATE）時就會激活它執行。

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性約束，它能夠對資料庫中的表做級聯修改，提供比CHECK約束更複雜的的數據完整性約束，並自定義錯誤消息。觸發器的主要作用有以下幾個方面：

  1. 強制資料庫間的引用完整性
  2. 級聯修改資料庫中所有相關的表，自動觸發其它與之相關的操作
  3. 跟蹤變化，撤銷或回滾違法操作，防止非法修改數據
  4. 返回自定義的錯誤消息，約束無法返回信息，而觸發器可以
  5. 觸發器可以調用更多的存儲過程

MySQL創建觸發器

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 語句創建觸發器。

語法格式如下：

  1. CREATE <觸發器名>< BEFORE | AFTER >
  2. <INSERT | UPDATE | DELETE >
  3. ON <表名> FOR EACH Row
  4. <觸發器主體>

語法說明如下。 (1) 觸發器名 觸發器的名稱，觸發器在當前資料庫中必須具有唯一的名稱。如果要在某個特定資料庫中創建，名稱前面應該加上資料庫的名稱。 (2) INSERT | UPDATE | DELETE觸發事件，用於指定激活觸發器的語句的種類。 註意：三種觸發器的執行時間如下。 INSERT：將新行插入表時激活觸發器。 DELETE： 從表中刪除某一行數據時激活觸發器。 UPDATE：更改表中某一行數據時激活觸發器。 (3) BEFORE | AFTER BEFORE 和 AFTER，觸發器被觸發的時刻，表示觸發器是在激活它的語句之前或之後觸發。若希望驗證新數據是否滿足條件，則使用 BEFORE 選項；若希望在激活觸發器的語句執行之後完成幾個或更多的改變，則通常使用 AFTER 選項。 (4) 表名 與觸發器相關聯的表名，此表必須是永久性表，不能將觸發器與臨時表或視圖關聯起來。 (5) 觸發器主體 觸發器動作主體，包含觸發器激活時將要執行的 MySQL 語句。如果要執行多個語句，可使用 BEGIN…END 複合語句結構。 在觸發器主體中，我們可以使用NEW表示將要插入的新行，OLD表示將要刪除的舊行。通過OLD，NEW獲取它們的欄位內容，方便在觸發操作中使用，下麵是對應事件是否支持OLD、NEW的對應關係：

事件 OLD NEW
INSERT × √
--- --- ---
DELETE √ ×
UPDATE √ √

由於UPDATE相當於刪除舊行（OLD），然後插入新行（NEW），所以UPDATE同時支持OLD、NEW。 (6) FOR EACH ROW一般是指行級觸發，對於受觸發事件影響的每一行都要激活觸發器的動作。例如，使用 INSERT 語句向某個表中插入多行數據時，觸發器會對每一行數據的插入都執行相應的觸發器動作。

MySQL分隔符（DELIMITER）

MySQL預設使用";"作為分隔符，SQL語句遇到";"就會提交。而我們的觸發器中可能會有多個";"符，為了防止觸發器創建語句過早的提交，我們需要臨時修改MySQL分隔符，創建完後，再將分隔符改回來。使用DELIMITER可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--觸發器創建語句；
  3. $ --提交創建語句；
  4. DELIMITER ;

MySQL刪除觸發器

  1. 可以使用drop trigger刪除觸發器： drop trigger trigger_name;
  2. 刪除前先判斷觸發器是否存在： drop trigger if exists trigger_name

MySQL查看觸發器

另外，在 MySQL 中，若需要查看資料庫中已有的觸發器，則可以使用 SHOW TRIGGERS 語句。 語法格式如下： SHOW TRIGGERS;

在 MySQL 中，所有觸發器的信息都存在 information_schema資料庫的 triggers表中，可以通過查詢命令 SELECT來查看，具體的語法如下： SELECT * FROM information_schema.triggers WHERE trigger_name= '觸發器名';

其中，'觸發器名'用來指定要查看的觸發器的名稱，需要用單引號引起來。這種方式可以查詢指定的觸發器，使用起來更加方便、靈活。

MySQL觸發器的使用

1** 、 MySQL 觸發器 Insert 觸發更新同一張表：** 　　下麵我們有一個表"tmp1"，tmp1表有兩個整型欄位：n1、n2。我們要通過觸發器實現，在tmp1插入記錄時，自動將n2欄位的值設置為n1欄位的5倍。 　創建測試表和觸發器：

  1. --創建測試表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --創建觸發器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

測試觸發更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

２、** MySQL 觸發器 Update **觸發更新另一張表： 　　下麵有有兩個表tmp1、tmp2，兩個表都有一個相同的欄位name。使用觸發器實現更新一個表的name時，將另外一個表的name也更新。 　創建測試表和觸發器：

  1. --創建測試表和插入測試數據
  2. drop table if exists tmp1;
  3. drop table if exists tmp2;
  4. create table tmp1 (id int, name varchar(128))default charset='utf8';
  5. create table tmp2 (fid int, name varchar(128))default charset='utf8';
  6. insert into tmp1 values(1,'愛E族');
  7. insert into tmp2 values(1,'愛E族');
  9. --創建觸發器
  10. drop trigger if exists tmp1_update;
  11. create trigger tmp1_update
  12. after update on tmp1
  13. for each row
  14. begin
  15. update tmp2 set name=new.name where fid=new.id;
  16. end;

測試觸發更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|愛E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|愛E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 1 row inset(0.00 sec)

編程要求

定義一個觸發器（tr_spj_delete），完成向SPJ表刪除數據時，及時更新所有供應商供應零件總數量。 根據提示，在右側編輯器補充代碼。

原始表結構及數據如下所示：

sno sname status city sqty
S1 精益 20 天津
--- --- --- --- ---
S2 盛錫 10 北京
S3 東方紅 30 北京
S4 豐泰盛 20 天津
S5 為民 30 上海
pno pname color weight
P1 螺母 紅 12
--- --- --- ---
P2 螺栓 綠 17
P3 螺絲刀 藍 14
P4 螺絲刀 紅 14
P5 凸輪 藍 40
P6 齒輪 紅 30
jno jname city
J1 三建 北京
--- --- ---
J2 一汽 長春
J3 彈簧廠 天津
J4 造船廠 天津
J5 機車廠 唐山
J6 無線電廠 常州
J7 半導體廠 南京
sno pno jno qty
S1 P1 J1 200
--- --- --- ---
S1 P1 J3 100
S1 P1 J4 700
S1 P2 J2 100
S2 P3 J1 400
S2 P3 J2 200
S2 P3 J4 500
S2 P3 J5 400
S2 P5 J1 400
S2 P5 J2 100
S3 P1 J1 200
S3 P3 J1 200
S4 P5 J1 100
S4 P6 J3 300
S4 P6 J4 200
S5 P2 J4 100
S5 P3 J1 200
S5 P6 J2 200
S5 P6 J4 500

測試說明

平臺會對你編寫的代碼進行測試：

預期輸出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_update AFTER UPDATE spj
  3. sno sname status city sqty
  4. S1 精益20天津1500
  5. S2 盛錫10北京1600
use demo;

#定義一個觸發器（tr\_spj\_update），完成向SPJ表更新數據時，及時更新所有供應商供應零件總數量。

#代碼開始

DELIMITER $$

CREATETRIGGER tr\_spj\_update AFTER UPDATEON spj FOREACHROW

BEGIN

    -- 如果供應商發生變化，更新舊供應商和新供應商的數量

    IF OLD.sno \<\> NEW.sno THEN

        UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;

        UPDATE s SET sqty = sqty + NEW.qty WHERE sno = NEW.sno;

    -- 否則，只更新數量變化的供應商

    ELSEIF OLD.qty \<\> NEW.qty THEN

        UPDATE s SET sqty = sqty +(NEW.qty - OLD.qty)WHERE sno = NEW.sno;

    END IF;

END$$

DELIMITER ;

UPDATE s

JOIN(

    SELECT sno,SUM(qty)AS total\_qty

    FROM spj

    GROUPBY sno

)AS t ON s.sno = t.sno

SET s.sqty = t.total\_qty;

#代碼結束

#以下代碼不要改動或刪除，將會對創建的觸發器進行測試

UPDATE spj SET sno='S1'WHERE sno='S2'AND pno='P3'AND jno='J1';

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 31基於java的旅游信息系統設計與實現
    基於java的旅游信息系統,景點信息管理,旅游路線管理，訂單信息管理，新聞信息管理,地方美食等。springboot的旅游管理系統，旅游系統. ...
  • 武裝你的WEBAPI-OData與DTO
    本文屬於OData系列文章 Intro 前面寫了很多有關OData使用的文章，很多讀者會有疑問，直接將實體對象暴露給最終用戶會不會有風險？$expand在預設配置的情況下，數據會不會有泄露風險？ 答案是肯定的，由於OData的特性，提供給我們便捷同時也會帶來一些風險。很多地方推薦使用DTO模式來隔離 ...
  • 記一次 .NET 某車零件MES系統 登錄異常分析
    一：背景 1. 講故事 這個案例有點特殊，以前dump分析都是和軟體工程師打交道，這次和非業內人士交流，隔行如隔山，從指導dump怎麼抓到問題解決，需要一個強大的耐心。 前幾天有位朋友在微信上找到我，說他們公司採購的MES系統登錄的時候出現了異常，讓我幫忙看一下，我在想解鈴還須系鈴人，怎麼的也不應該 ...
  • 武裝你的WEBAPI-OData之API版本管理
    本文屬於OData系列 Intro 對外提供WEBAPI時，如果遇上了版本升級，那麼控制WEBAPI的版本也是非常必要的。OData官方提供了版本控制以及管理的解決方案，我個人是實踐體會是不好用，好在社區提供了對應的nuget包，與.NET主版本同步更新。 介紹 ASP.NET API Versio ...
  • shell自動化腳本，啟動、停止應用程式
    #!/usr/bin/env bash # 常量初始化 set_runtime_vars(){ # 日期時間 Now_Date=`date +"%Y-%m-%d %H:%M:%S"` # 服務狀態 Service_Status=( "服務已開啟" "服務已停止" ) # 動作狀態 Action_St ...
  • 伺服器卡死，ssh連接不上-finalshell導致
    問題：客戶反饋伺服器ssh連接不上 設備信息：Asianux Server release 7.3.1512（Lotus） 定位：通過虛擬機控制台重啟伺服器，發現能登錄虛擬機，查看伺服器各種狀態都正常。 客戶使用一段時間後又卡死，ssh連接不上，登錄發現進程數有一萬多個，導致記憶體不足，伺服器卡斷 查 ...
  • 筆記本通過HDMI介面擴展顯示器，微信/Outlook等界面模糊變清晰的解決辦法
    1、筆記本擴展顯示器，微信界面顯示字體模糊如何解決？ 解決方案： 第一步：滑鼠右鍵打開微信快捷方式，選擇‘屬性’，找到‘相容性’，選擇‘ 更改高DPI設置’ 第二步：高DPI縮放替代：勾選✔ ‘替代高DPI縮放行為’ 第三步：點擊“確定”。 第四步：重新啟動微信，微信界面的字體顯示清晰了 2、問題描 ...
  • 實驗六 存儲過程
    #實驗六 存儲過程 第1關：增加供應商相關列sqty use demo; #代碼開始 #在S表中增加一列供應零件總數量（sqty），預設值為0。 altertable s add sqty intdefault0; #代碼結束 desc s; 第2關：定義、調用簡單存儲過程 use demo; #代 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類