数据库：一个例子搞懂三种触发器

一.准备工作---示例的表和数据

mysql> use  mydb16_trigger;

mysql> create table goods(
    -> gid char(8) primary key,
    -> name varchar(10),
    -> price decimal(8,2),
    -> num int);

mysql> create table orders(
    -> oid int primary key auto_increment,
    -> gid char(10) not null,
    -> name varchar(10),
    -> price decimal(8,2),
    -> onum int,
    -> otime date);

mysql> insert into goods values
    -> ('A0001','橡皮',2.5,100),
    -> ('B0001','小楷本',2.8,210),
    -> ('C0001','铅笔',1.2,120),
    -> ('D0001','计算器',28,20);

表结构和数据：

mysql> desc goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid   | char(8)      | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| price | decimal(8,2) | YES  |     | NULL    |       |
| num   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc orders;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| oid   | int          | NO   | PRI | NULL    | auto_increment |
| gid   | char(10)     | NO   |     | NULL    |                |
| name  | varchar(10)  | YES  |     | NULL    |                |
| price | decimal(8,2) | YES  |     | NULL    |                |
| onum  | int          | YES  |     | NULL    |                |
| otime | date         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>
mysql> insert into goods values
    -> ('A0001','橡皮',2.5,100),
    -> ('B0001','小楷本',2.8,210),
    -> ('C0001','铅笔',1.2,120),
    -> ('D0001','计算器',28,20);
Query OK, 4 rows affected (0.01 sec)

二.创建触发器&测试

1.INSERT 触发器

构建：

eg.订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试

mysql> create trigger orders_insert_trigger
    -> after insert on orders for each row  
    -> update goods set num=num-new.onum where gid=new.gid;

 

1. CREATE TRIGGER orders_insert_trigger

• 这部分代码的作用是创建一个触发器，并且给这个触发器命名为 orders_insert_trigger。在 MySQL 里，触发器的名称在同一个数据库中必须是唯一的。

2. AFTER INSERT ON orders

• AFTER 明确了触发器的触发时机，即当 orders 表执行 INSERT 操作之后触发该触发器。除了 AFTER，还可以使用 BEFORE，表示在操作执行之前触发。
• INSERT 是触发事件，意味着当向 orders 表插入新记录时，触发器会被激活。
• ON orders 指出该触发器是与 orders 表关联的，也就是说只有 orders 表发生插入操作时，触发器才会起作用。

3. FOR EACH ROW

• 此语句表明该触发器是行级触发器，即对于 INSERT 操作所影响的每一行记录，触发器都会执行一次。与之相对的是语句级触发器（不过 MySQL 不支持语句级触发器），语句级触发器无论操作影响多少行，都只会执行一次。

4. UPDATE goods SET num = num - NEW.onum WHERE gid = NEW.gid;核心部分

• 这是触发器的主体部分，是一个 UPDATE 语句，用于更新 goods 表的数据。
  • UPDATE goods：指定要更新的表为 goods 表。
  • SET num = num - NEW.onum：定义更新的规则，将 goods 表中 num 列（代表商品库存数量）的值减去新插入订单的商品数量。NEW 是 MySQL 触发器中的一个特殊关键字，它代表新插入的行记录。NEW.onum 表示新插入订单记录中的 onum 列的值（即订单中的商品数量）。
  • WHERE gid = NEW.gid：这是更新的条件，确保只更新 goods 表中 gid（商品 ID）与新插入订单记录中的 gid 相等的商品记录。

检测: 

eg.在orders表插入订单，检测goods表对应商品数目是否减少

insert into orders(gid,name,price,onum,otime) values('A0001','橡皮',2.5,10,'2025-02-08');

结果及分析： 

 

2.DELETE 触发器

构建:

eg.实现功能:客户取消订单，恢复商品表对应商品的数量

mysql> create trigger orders_delete_trigger
    -> after delete on orders for each row
    -> update goods set num=num+old.onum where gid=old.gid;

代码结构和前者差不多，不过多赘述了。

检测：

eg.删除orders表的一个订单，检查goods表商品数目是否恢复

 delete from orders where oid=1;

结果及分析：

3.UPDATE 触发器

构建:

eg.实现功能:客户修改订单，商品表对应商品数量同步更新

mysql> create trigger orders_update_trigger
    -> after update on orders for each row
    -> update goods set num=num+old.onum-new.onum where gid=new.gid;

检测：

eg.原本goods表中B0001为210件，现在向orders插入一个订单10件，goods表中B0001应该减少10件为200.但是现在又修改订单为5件，即210-10+5=205.

insert into orders(gid,onum) values('B0001',10);
update orders set onum=5 where oid=2;

结果和分析：