数据库_MySQL: mysql触发器(trigger)

74 阅读 0 评论 49 点赞

我是靠谱客的博主无聊小蝴蝶，这篇文章主要介绍数据库_MySQL: mysql触发器(trigger)，现在分享给大家，希望可以做个参考。

更过MySQL使用操作，点击查看专栏: MySQL数据库_命令详解

三.触发器(trigger)

在JS中，当用户鼠标操作、键盘操作都会触发对应的事件，执行相对的JS代码.

在MySQL中，提前设置好触发器（trigger），当用户执行指定SQL语句时就会自动触发。

1.创建触发器

语法:

复制代码

1
2
3
4
5
6
7
8
9
delimiter //
create trigger 触发器名称 before|after 事件 on 表名 for each row
begin
    #SQL语句
end //

delimiter ;

before | after 在执行增/改/删SQL语句执行之前或之后触发

事件 update/delete/insert

表名声明哪张表被监控

for each row 声明一次性影响多条数据则触发N次

创建表a和表b，当a表插入数据后，b表也自动插入数据

复制代码

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
create table a (id int)engine=myisam charset=utf8;
create table b (id int)engine=myisam charset=utf8;

#创建触发器
drop trigger if exists t1;
delimiter //
#监控a表，当a表插入数据时执行该触发器
create trigger t1 after insert on a for each row
begin
#SQL语句
insert into b values(9);
end //
delimiter ;

insert into a values (1);

mysql> select * from a;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from b;
+------+
| id   |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

2.触发器相关SQL语句

删除触发器：drop trigger [if exists] 触发器名;

显示所有触发器：show triggersG

显示创建触发器的SQL语句：show create trigger 触发器名称;

文章评论的增加

复制代码

#创建文章
create table article(
id int unsigned primary key auto_increment comment '文章ID',
title varchar(120) not null comment '文章标题',
content text not null comment '文章内容',
created_time int not null comment '创建于',
updated_time int not null comment '更新于',
comment_count int not null default 0 comment '总评论数'
)engine=myisam charset=utf8;

#文章表插入两条测试数据
insert into article values
(1,'aa','asdadf',unix_timestamp(),unix_timestamp(),0),
(2,'aa','asdadf',unix_timestamp(),unix_timestamp(),0);

#创建评论表
create table comment(
article_id int not null comment '关联文章表',
id int unsigned primary key auto_increment comment '评论ID',
content text not null comment '评论内容',
created_time int not null comment '创建于',
updated_time int not null comment '更新于'
)engine=myisam charset=utf8;

#触发器
delimiter //
create trigger t2 after insert on comment for each row
begin
     #new关键词代表新插入的数据
     update article set comment_count = comment_count + 1 where id  = new.article_id;
end //
delimiter ;

#给评论表插入一条数据
mysql> insert into comment values (1, null, 'aaaa', unix_timestamp(), unix_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from article;                                                   +----+-------+---------+--------------+--------------+---------------+
| id | title | content | created_time | updated_time | comment_count |
+----+-------+---------+--------------+--------------+---------------+
|  1 | aa    | asdadf  |   1552833768 |   1552833768 |             1 |
|  2 | aa    | asdadf  |   1552833768 |   1552833768 |             0 |
+----+-------+---------+--------------+--------------+---------------+
2 rows in set (0.00 sec)

mysql> insert into comment values (1, null, 'aaaa', unix_timestamp(), unix_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from article;                                                   +----+-------+---------+--------------+--------------+---------------+
| id | title | content | created_time | updated_time | comment_count |
+----+-------+---------+--------------+--------------+---------------+
|  1 | aa    | asdadf  |   1552833768 |   1552833768 |             2 |
|  2 | aa    | asdadf  |   1552833768 |   1552833768 |             0 |
+----+-------+---------+--------------+--------------+---------------+
2 rows in set (0.00 sec)

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
#创建文章
create table article(
id int unsigned primary key auto_increment comment '文章ID',
title varchar(120) not null comment '文章标题',
content text not null comment '文章内容',
created_time int not null comment '创建于',
updated_time int not null comment '更新于',
comment_count int not null default 0 comment '总评论数'
)engine=myisam charset=utf8;

#文章表插入两条测试数据
insert into article values
(1,'aa','asdadf',unix_timestamp(),unix_timestamp(),0),
(2,'aa','asdadf',unix_timestamp(),unix_timestamp(),0);

#创建评论表
create table comment(
article_id int not null comment '关联文章表',
id int unsigned primary key auto_increment comment '评论ID',
content text not null comment '评论内容',
created_time int not null comment '创建于',
updated_time int not null comment '更新于'
)engine=myisam charset=utf8;

#触发器
delimiter //
create trigger t2 after insert on comment for each row
begin
     #new关键词代表新插入的数据
     update article set comment_count = comment_count + 1 where id  = new.article_id;
end //
delimiter ;

#给评论表插入一条数据
mysql> insert into comment values (1, null, 'aaaa', unix_timestamp(), unix_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from article;                                                   +----+-------+---------+--------------+--------------+---------------+
| id | title | content | created_time | updated_time | comment_count |
+----+-------+---------+--------------+--------------+---------------+
|  1 | aa    | asdadf  |   1552833768 |   1552833768 |             1 |
|  2 | aa    | asdadf  |   1552833768 |   1552833768 |             0 |
+----+-------+---------+--------------+--------------+---------------+
2 rows in set (0.00 sec)

mysql> insert into comment values (1, null, 'aaaa', unix_timestamp(), unix_timestamp());
Query OK, 1 row affected (0.00 sec)

mysql> select * from article;                                                   +----+-------+---------+--------------+--------------+---------------+
| id | title | content | created_time | updated_time | comment_count |
+----+-------+---------+--------------+--------------+---------------+
|  1 | aa    | asdadf  |   1552833768 |   1552833768 |             2 |
|  2 | aa    | asdadf  |   1552833768 |   1552833768 |             0 |
+----+-------+---------+--------------+--------------+---------------+
2 rows in set (0.00 sec)

3.关键字: new,old

触发器	new代表新数据	old代表就数据
insert	新添加的数据	——
delete	——	保存删除的数据
update	更新后数据	更新前数据

创建insert触发器，添加的新数据自动存储在触发器内部的new关键字中

创建delete触发器，被删除的数据自动存储在触发器内部的old关键字中

创建update触发器，被更新的数据自动存储在触发器内部的old/new关键字中

触发器内部获取语法：new.字段名或 old.字段名

当c表数据被增/改/删时，在log表中添加记录

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#设置字符集
set names gbk;

#测试c表
create table c(
id int
)engine=myisam charset=utf8;

#日志log表
create table log(
id int primary key auto_increment comment '编号',
comment varchar(120) not null default '' comment '说明'
)engine=myisam charset=utf8;

增记录：【c表增加数据ID为：1】

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter //
create trigger t3 after insert on c for each row
begin
    #new关键词  保存刚刚insert的数据
    insert into log values (null, concat('c表增加数据ID为：',new.id));
end //
delimiter ;

insert into c  values (1);
insert into c  values (99);

mysql> select * from c;
+------+
| id   |
+------+
|    1 |
|   99 |
+------+
2 rows in set (0.00 sec)

改记录：【c表修改数据ID为：1】

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter //
create trigger t4 after update on c for each row
begin
#new关键词  保存刚刚修改后数据
#old关键词  保存的是修改前数据
    insert into log values (null, concat('c表修改数据ID为：',old.id));
end //
delimiter ;

update c  set id = 111 where id = 1;

mysql> select * from c;
+------+
| id   |
+------+
|  111 |
|   99 |
+------+
2 rows in set (0.00 sec)

删记录：【c表删除数据ID为：1】

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
delimiter //
create trigger t5 after delete on c for each row
begin
#old关键词  保存的是删除前数据
    insert into log values (null, concat('c表删除数据ID为：',old.id));
end //
delimiter ;

delete from c where id = 99;

mysql> select * from c;
+------+
| id   |
+------+
|  111 |
+------+
1 row in set (0.00 sec)