主頁 > 資料庫 > 實驗七 函式與觸發器

實驗七 函式與觸發器

2023-05-10 09:47:28 資料庫

實驗七 函式與觸發器

第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';

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/552057.html

標籤：其他

上一篇：華為云資料庫首席專家談分布式資料應用挑戰和發展建議

下一篇：返回列表

標籤雲
其他(158750) Python(38124) JavaScript(25412) Java(18024) C(15222) 區塊鏈(8263) C＃(7972) AI(7469) 爪哇(7425) MySQL(7175) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5871) 数组(5741) R(5409) Linux(5336) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4570) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2432) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1965) Web開發(1951) HtmlCss(1934) python-3.x(1918) 弹簧靴(1913) C++(1912) xml(1889) PostgreSQL(1875) .NETCore(1857) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • 實驗七 函式與觸發器

    #實驗七 函式與觸發器 第1關：定義、呼叫引數函式 相關知識 MySQL存盤函式 存盤函式和存盤程序一樣，都是在資料庫中定義一些 SQL 陳述句的集合。存盤函式可以通過 return 陳述句回傳函式值，主要用于計算并回傳一個值。而存盤程序沒有直接回傳值，主要用于執行操作。 在 MySQL 中，使用 CR ......

    uj5u.com 2023-05-10 09:47:28 more
  • 華為云資料庫首席專家談分布式資料應用挑戰和發展建議

    摘要：本文分析了分布式資料庫發展情況、分布式資料庫應用的主要問題，從行業應用的角度給出了分布式資料庫發展的建議。 本文分享自華為云社區《數字化轉型下我國分布式資料庫應用挑戰及發展建議》，作者：資料庫領域科學家、華為云資料庫GaussDB首席專家 馮柯。 當前，金融等重點行業都在進行數字化轉型，而分布 ......

    uj5u.com 2023-05-10 09:47:23 more
  • oracle例外處理

    序言 最近在作業中遇到這么一個場景： 在同一網段記憶體在著A庫和B庫，需要將A庫下某些表的資料同步到B庫 B庫跑著定時任務，定時呼叫存盤程序將A庫下的資料同步到B庫。 B庫和A庫是通過建立dblink建立連接的。【關于dblink相關可能會后面單獨寫博客，先給自己挖個坑，慢慢填 哈哈】。 定時任務的頻 ......

    uj5u.com 2023-05-10 09:45:54 more
  • 如何從800萬資料中快速撈出自己想要的資料？

    (如何從800萬資料中快速撈出自己想要的資料) 一、需求調研 正如題目所說，我們使用的是Oracle資料庫，資料量在800萬左右。我們要完成的事情就是在著800萬資料中，通過某些欄位進行模糊查詢，得到我們所需要的結果集。 這是表里的資料，一共7328976 條資料，接近800萬 select cou ......

    uj5u.com 2023-05-10 09:39:50 more
  • oracle例外處理

    序言 最近在作業中遇到這么一個場景： 在同一網段記憶體在著A庫和B庫，需要將A庫下某些表的資料同步到B庫 B庫跑著定時任務，定時呼叫存盤程序將A庫下的資料同步到B庫。 B庫和A庫是通過建立dblink建立連接的。【關于dblink相關可能會后面單獨寫博客，先給自己挖個坑，慢慢填 哈哈】。 定時任務的頻 ......

    uj5u.com 2023-05-10 09:39:34 more
  • 實驗六 存盤程序

    #實驗六 存盤程序 第1關：增加供應商相關列sqty use demo; #代碼開始 #在S表中增加一列供應零件總數量（sqty），默認值為0。 altertable s add sqty intdefault0; #代碼結束 desc s; 第2關：定義、呼叫簡單存盤程序 use demo; #代 ......

    uj5u.com 2023-05-10 09:39:12 more
  • 實驗七 函式與觸發器

    #實驗七 函式與觸發器 第1關：定義、呼叫引數函式 相關知識 MySQL存盤函式 存盤函式和存盤程序一樣，都是在資料庫中定義一些 SQL 陳述句的集合。存盤函式可以通過 return 陳述句回傳函式值，主要用于計算并回傳一個值。而存盤程序沒有直接回傳值，主要用于執行操作。 在 MySQL 中，使用 CR ......

    uj5u.com 2023-05-10 09:39:08 more
  • 華為云資料庫首席專家談分布式資料應用挑戰和發展建議

    摘要：本文分析了分布式資料庫發展情況、分布式資料庫應用的主要問題，從行業應用的角度給出了分布式資料庫發展的建議。 本文分享自華為云社區《數字化轉型下我國分布式資料庫應用挑戰及發展建議》，作者：資料庫領域科學家、華為云資料庫GaussDB首席專家 馮柯。 當前，金融等重點行業都在進行數字化轉型，而分布 ......

    uj5u.com 2023-05-10 09:39:03 more
  • 作業中，Oracle常用函式

    (作業中，Oracle常用函式) 1、序言 Oracle提供了不少內置函式，熟練使用這些函式，可以大大提高我們作業效率。函式可以接受0個或多個入參，并回傳一個輸出結果。 2、Oracle函式分類 Oracle函式分為單行函式和聚合函式 單行函式：對每一個函式應用在表的記錄中時，只能輸入一行結果，回傳 ......

    uj5u.com 2023-05-09 08:26:27 more
  • 存盤程序

    存盤程序 介紹 存盤程序是事先經過編譯并存盤在資料庫中的一段SQL陳述句的集合，呼叫存盤程序可以簡化應用開發人員的很多作業，減少資料在資料庫和應用服務器之間的傳輸，對于提高資料處理的效率是有好處的。 存盤程序思想上很簡單，就是資料庫SQL語言層面的代碼封裝與重用，類似于函式或者說API，封裝了系列操作 ......

    uj5u.com 2023-05-09 08:26:15 more

Copyright © 2010-2020 有解無憂