MySQL 9从入门到性能优化-创建触发器
【图书推荐】《MySQL 9从入门到性能优化(视频教学版)》-CSDN博客
《MySQL 9从入门到性能优化(视频教学版)(数据库技术丛书)》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)
MySQL9数据库技术_夏天又到了的博客-CSDN博客
触发器是一个特殊的存储过程,它与普通的存储过程不同的是:存储过程的执行要使用CALL语句来调用,而触发器的执行不需要使用CALL语句来调用,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被MySQL自动调用。例如,当对表fruits进行操作(INSERT、DELETE或UPDATE)时,就会激活它触发器。
触发器不仅可以查询其他表,而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如,可以根据客户当前的账户状态,控制是否允许插入新订单。本节将介绍如何创建触发器。
10.1.1 创建只有一个执行语句的触发器
创建只有一个执行语句的触发器的语法如下:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
其中,trigger_name表示触发器名称,用户自行指定;trigger_time表示触发时机,可以指定为before或after;trigger_event表示触发事件,包括INSERT、UPDATE和DELETE;tbl_name表示建立触发器的表名,即在哪张表上建立触发器;trigger_stmt是触发器的执行语句。
【例10.1】创建一个单执行语句的触发器,SQL语句如下:
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql>SET @sum =0;
mysql> INSERT INTO account VALUES(1,1.00), (2,2.00);
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
对于上述语句,首先创建一张表account,表中有两个字段,分别为acct_num(定义为INT类型)和amount(定义成浮点类型);其次,创建一个名为ins_sum的触发器,触发的条件是向数据表account插入数据之前,对新插入的amount字段值进行求和计算。
10.1.2 创建有多个执行语句的触发器
创建有多个执行语句的触发器的语法如下:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW
BEGIN
语句执行列表
END
其中,trigger_name表示触发器的名称,用户自行指定;trigger_time表示触发时机,可以指定为before或after;trigger_event表示触发事件,包括INSERT、UPDATE和DELETE;tbl_name表示建立触发器的表名,即在哪张表上建立触发器;触发器程序可以使用BEGIN和END作为开始和结束,中间包含多条语句。
【例10.2】创建一个包含多个执行语句的触发器,SQL语句如下:
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER //
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
//
DELIMITER ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
上面的代码创建了一个名为“testref”的触发器。这个触发器的触发条件是在向表test1插入数据前执行触发器的语句,具体执行的代码如下:
mysql> INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
4张表中的数据如下:
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
执行结果显示,在向表test1插入记录的时候,表test2、表test3、表test4都发生了变化。从这个例子中可以看到,表test1中的INSERT操作触发了触发器,向表test2中插入了表test1中的值,删除了表test3中相同的内容,同时更新了表test4中的b4,即与插入的值相同的个数。