MySQL触发器实战：给新用户发邮件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
触发器，trigger，用于【监视某种情况】并【触发某种操作】，
它是与表时间相关的特殊的【存储过程】，它的【执行不是由程序调用】，
而是【由事件来触发】。例如，当对某一张表进行insert,delete,update操作室
就会触发执行它。

创建触发器语法
create trigger trigger_name trigger_time trigger_event on table_name for each row
trigger_stmt

参数说明：
trigger_name:触发器名称
trigger_time:触发时间，取值有before,after
trigger_event:触发事件，取值有insert,delete,update
table_name:触发器监控的表名
trigger_stmt:触发器执行的语句，可以使用old,new来引用变化前后的记录内容
	new.columnName:获取insert触发时间中新插入的数据
	old.columnName:获取UPDATE和DELETE触发器时间中被更新、删除的数据

测试数据及需求描述
drop table if exists user_info;
drop table if exists email_info;

create table user_info(
	id int not auto_increment primary key,
	name varchar(30),
	email varchar(50)
);

insert into user_info(id,name,email) values (1,'mofei','mofei@qq.com');
insert into user_info(id,name,email) values (2,'张三','zhangsan@qq.com');

create table email_info(
	id int not null auto_increment primarykey,
	email varchar(50),
	content text,
	send_time datetime
);

当有新用户插入时，自动给用户发送邮件。
实战：给新用户发邮件
创建触发器
delimiter //
create trigger send_email_trigger after  insert on user_info for each row
begin 
	insert into email_info(email,content,send_time) values(NEW.email,'欢迎加入MySQL阵营！',now());
end
//
delimiter ;


mysql> delimiter //
mysql> create trigger send_email_trigger after  insert on user_info for each row
    -> begin
    -> insert into email_info(email,content,send_time) values(NEW.email,'欢迎加入MySQL阵营！',now());
    -> end
    -> //
Query OK, 0 rows affected (4.18 sec)

插入数据


mysql> select * from email_info;
+----+-----------------+---------------------+---------------------+
| id | email           | content             | send_time           |
+----+-----------------+---------------------+---------------------+
|  1 | zhangsan@qq.com | 欢迎加入MySQL阵营！ | 2020-04-10 10:47:36 |
|  2 | zhangsan@qq.com | 欢迎加入MySQL阵营！ | 2020-04-10 11:10:16 |
|  3 | mofei@qq.com    | 欢迎加入MySQL阵营！ | 2020-04-10 11:11:59 |
+----+-----------------+---------------------+---------------------+
3 rows in set (0.15 sec)

mysql> insert into user_info(id,name,email) values (4,'lisi','lisi@qq.com');
Query OK, 1 row affected (1.04 sec)

mysql> select * from email_info;
+----+-----------------+---------------------+---------------------+
| id | email           | content             | send_time           |
+----+-----------------+---------------------+---------------------+
|  1 | zhangsan@qq.com | 欢迎加入MySQL阵营！ | 2020-04-10 10:47:36 |
|  2 | zhangsan@qq.com | 欢迎加入MySQL阵营！ | 2020-04-10 11:10:16 |
|  3 | mofei@qq.com    | 欢迎加入MySQL阵营！ | 2020-04-10 11:11:59 |
|  4 | lisi@qq.com     | 欢迎加入MySQL阵营！ | 2020-04-10 17:45:25 |
+----+-----------------+---------------------+---------------------+