mysql触发器学习
时间：2021-07-01 10:21:17
帮助过：3人阅读
)是一个特殊的存储过程，他的执行不是由程序调用，也不是手工启动，而是由事件触发，比如当对一个表进行操作（
insert,
delete,
update）
时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。
因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数保持一致。
创建Trigger;
语法：
CREATE TRIGGER 触发器名称 BEFORE
|AFTER 触发事件
ON 表名
FOR EACH ROW
BEGIN
触发器程序体;
END
<触发器名称
> 最多64个字符，它和mysql中其他对象的命名方式一样。
{BEFORE|AFTER} 触发器时机
{INSERT|UPDATE|DELETE} 触发器事件
ON<表名称
> 标识触发器的表名，即在那张表上建立触发器。
FOR EACH ROW 触发器的执行间隔:
FOR EACH ROW 子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。
<触发器程序体
> 触发器所要触发的SQL语句:语句可以使用顺序，判断，循环等语句，实现一般程序所需要的逻辑功能。
同一张表最多可以创建6个触发器(分别是insert BEFORE|AFTER...)
创建学生表：
mysql> create table student(
-> id
int auto_increment
primary key not null,
-> name
varchar(
50)
-> );
Query OK, 0 rows affected (
0.02 sec)
插入一条数据：
mysql> insert into student
values(
1,
‘jack‘);
Query OK, 1 row affected (
0.00 sec)
创建student_total表：
mysql> create table student_total(total
int);
Query OK, 0 rows affected (
0.01 sec)
example1:
.创建存储器student_insert_trigger
mysql> delimiter
&&
mysql> create trigger student_insert_trigger after
insert
> on student
for each row
> BEGIN
> update student_total
set total
=total
+1;
> END &&
mysql> delimiter ;
mysql> insert into student
values(
2,
‘tom‘);
Query OK, 1 row affected (
0.01 sec)
mysql> select * from student_total;
+-------+
| total
|
+-------+
| 2 |
+-------+
1 row
in set (
0.01 sec)
创建删除的trigger：
mysql> create trigger student_delete_after after
delete
on student
for each row
BEGIN
update student_total
set total
=total
-1;
END$$
Query OK, 0 rows affected (
0.00 sec)
mysql> delimiter ;
测试一下：
插入数据前：
mysql> select * from student;
+----+--------+
| id
| name
|
+----+--------+
| 1 | jack
|
| 2 | tom
|
| 3 | jerry
|
| 10 | jastin
|
+----+--------+
4 rows
in set (
0.00 sec)
mysql> select * from student_total;
+-------+
| total
|
+-------+
| 4 |
+-------+
1 row
in set (
0.00 sec)
删除一条记录：
mysql> delete from student
where name
=‘jastin‘;
Query OK, 1 row affected (
0.00 sec)
mysql> select * from student;
+----+-------+
| id
| name
|
+----+-------+
| 1 | jack
|
| 2 | tom
|
| 3 | jerry
|
+----+-------+
3 rows
in set (
0.00 sec)
mysql> select * from student_total;
+-------+
| total
|
+-------+
| 3 |
+-------+
1 row
in set (
0.00 sec)
可以看到total数变为3;
查看触发器：
mysql> show triggers\G
*************************** 1. row
***************************
Trigger: student_insert_trigger
Event: INSERT
Table: student
Statement: BEGIN
update student_total
set total
=total
+1;
END
Timing: AFTER
Created: 2017-04-03 00:
59:
37.56
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row
***************************
Trigger: student_delete_after
Event: DELETE
Table: student
Statement: BEGIN update student_total
set total
=total
-1;
END
Timing: AFTER
Created: 2017-04-03 01:
19:
05.27
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows
in set (
0.01 sec)
也可以在information_schema库中查看：
mysql> select * from information_schema.triggers\G
删除触发器：
mysql> drop trigger student_delete_after;
Query OK, 0 rows affected (
0.02 sec)
触发器实例：
example2
作用：增加tab1表记录后自动将记录增加到tab2表中
创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
tab1_id varchar(
50)
);
创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
tab2_id varchar(
50)
);
创建触发器：
delimiter $$
mysql> DROP TRIGGER IF EXISTS tab1_after_trigger;
Query OK, 0 rows affected,
1 warning (
0.00 sec)
CREATE TRIGGER tab1_after_trigger
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id)
values(new.tab1_id);
END
delimiter ;
mysql> select * from tab1;
Empty set (
0.01 sec)
mysql> select * from tab2;
Empty set (
0.00 sec)
向tab1中插入一条数据：
mysql> insert into tab1
values(
‘yang‘);
Query OK, 1 row affected (
0.00 sec)
mysql> select * from tab1;
+---------+
| tab1_id
|
+---------+
| yang
|
+---------+
1 row
in set (
0.00 sec)
mysql> select * from tab2;
+---------+
| tab2_id
|
+---------+
| yang
|
+---------+
1 row
in set (
0.00 sec)
example 3：
创建一个触发器，当student1表有一个更新操作的时候触发更新update_student1表
创建student1表：
mysql> create table student1(
-> student_id
int auto_increment
primary key,
not null,
-> student_name
varchar(
30)
not null,
-> student_sex enum(
‘f‘,
‘m‘)
-> );
Query OK, 0 rows affected (
0.01 sec)
mysql> desc student1;
+--------------+---------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+--------------+---------------+------+-----+---------+----------------+
| student_id
| int(
11)
| NO
| PRI
| NULL | auto_increment
|
| student_name
| varchar(
30)
| NO
| | NULL | |
| student_sex
| enum(
‘f‘,
‘m‘)
| YES
| | NULL | |
+--------------+---------------+------+-----+---------+----------------+
3 rows
in set (
0.00 sec)
mysql> insert into student1
values
-> (
1,
‘book‘,
‘m‘),
-> (
2,
‘robin‘,
‘m‘),
-> (
3,
‘alice‘,
‘f‘)
-> ;
Query OK, 3 rows affected (
0.00 sec)
Records: 3 Duplicates:
0 Warnings:
0
创建update_student1表：
mysql> create table update_student1(
-> update_record
int auto_increment
primary key not null,
-> student_id
int not null,
-> update_date date
-> );
Query OK, 0 rows affected (
0.01 sec)
mysql> desc update_student1;
+---------------+---------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+---------------+---------+------+-----+---------+----------------+
| update_record
| int(
11)
| NO
| PRI
| NULL | auto_increment
|
| student_id
| int(
11)
| NO
| | NULL | |
| update_date
| date
| YES
| | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows
in set (
0.01 sec)
mysql> insert into student1
values
-> (
1,
‘book‘,
‘m‘),
-> (
2,
‘robin‘,
‘m‘),
-> (
3,
‘alice‘,
‘f‘)
-> ;
Query OK, 3 rows affected (
0.00 sec)
Records: 3 Duplicates:
0 Warnings:
0
delimiter $$
create trigger student_update_trigger before
update
on student1
for each row
begin
if new.student_id
!=old.student_id
then #如果st8udent1表中的student_id和旧的student_id不一样，就触发更新update_student1表；也就是说student1中的student_id变了，update_student1表中的student_id也会被触发更新；
update update_student1
set student_id
=new.student_id
where student_id
=old.student_id;
end if;
end$$
delimiter ;
mysql> create trigger student1_update_trigger before
update
-> on student1
for each row
-> begin
-> if new.student_id
!=old.student_id
then
-> update update_student1
-> set student_id
=new.student_id
-> where student_id
=old.student_id;
-> end if
-> ;
-> end$$
Query OK, 0 rows affected (
0.01 sec)
mysql> delimiter ;
再创建一个删除表id的触发器：
mysql> delimiter $$
mysql> create trigger student1_delete_trigger before
delete
-> on student1
for each row
-> begin
-> delete from update_student1
-> where student_id
=old.student_id;
-> end$$
Query OK, 0 rows affected (
0.01 sec)
修改student1表中的student_id字段，验证触发器：
mysql> update student1
set student_id
=101 where student_name
=‘book‘;
Query OK, 1 row affected (
0.01 sec)
Rows matched: 1 Changed:
1 Warnings:
0
mysql> select * from student1;
+------------+--------------+-------------+
| student_id
| student_name
| student_sex
|
+------------+--------------+-------------+
| 2 | robin
| m
|
| 3 | alice
| f
|
| 101 | book
| m
|
+------------+--------------+-------------+
3 rows
in set (
0.01 sec)
mysql> select * from update_student1;
+---------------+------------+-------------+
| update_record
| student_id
| update_date
|
+---------------+------------+-------------+
| 1 | 101 | 2017-04-06 |
| 2 | 2 | 2017-04-06 |
| 3 | 3 | 2017-04-06 |
+---------------+------------+-------------+
3 rows
in set (
0.00 sec)
可以发现update_student1表中的student_id字段也改变了
还没有写完，待续。。。。。。。。。。。
mysql触发器学习
标签：程序 建表 sub 计算 style hang error char 启动
