mysql中触发器if mysql中触发器可以实现吗

文章目录

• 一、概述

• （一）什么是触发器
• （二）触发器与存储过程的异同
• （三）触发器的作用

• 二、创建触发器
• 三、查看触发器

• （一）SHOW TRIGGERS 语句查看触发器
• （二）查看系统表 triggers 实现查看触发器

• 四、删除触发器
• 五、触发器类型NEW和OLD的使用

---

一、概述

（一）什么是触发器

在实际开发中往往会碰到这样的情况：

当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用 sql语句进行更新，将需要执行多条操作语句！

比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新。而以上的场景，我们可以轻松使用触发器来实现！

• 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。只有当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL 从 5.0.2 版本开始支持触发器。
• 触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行
• 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作；
• 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发；

（二）触发器与存储过程的异同

相同点：1. 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段。
不同点：2. 存储器调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。

（三）触发器的作用

1. 可在写入数据前，强制检验或者转换数据(保证护数据安全)
2. 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
    

二、创建触发器

在 MySQL 中创建触发器通过 SQL 语句 CREATE TRIGGER 来实现，

语法形式：

CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW trigger_STMT

在上述语句中，参数 trigger_name 表示要创建的触发器名；参数 BEFORE 和 AFTER 指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；参数 trigger_EVENT 表示触发事件，即触发器执行条件，包含 DELETE、INSERT 和 UPDATE 语句；参数TABLE_NAME 表示触发事件的操作表名；参数 FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器；参数 trigger_STMT 表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于 INSERT语句,只有 NEW 是合法的，表示当前已插入的记录；对于 DELETE 语句，只有 OLD 才合法，表示当前删除的记录；而 UPDATE 语句可以和 NEW（更新后）以及 OLD（更新前）同时使用

注意：

1.不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看 MySQL 中是否已经存在该标识符的触发器和触发器的相关事件

2.在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器

3.每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

实例：

use school; #选择数据库 school
CREATE TABLE class (`id` int NOT NULL AUTO_INCREMENT, 
                    `name` varchar(128) DEFAULT NULL, 
                    `teacher` varchar(64) DEFAULT NULL, 
                    `count` int DEFAULT 0, 
                    UNIQUE KEY `id` (`id`)
); #创建班级表 class

insert into class values(101, '萌新一班', 'Martin', 0),(102, '萌新二班', 'Rock', 0),(103, ' 萌新三班', 'Janny', 0); 

#创建成绩表 grade

CREATE TABLE `student` (`id` int NOT NULL AUTO_INCREMENT UNIQUE, 
                        `name` varchar(64) DEFAULT NULL, 
                        `class_id` int DEFAULT NULL, 
                        `sex` enum('F','M') DEFAULT NULL
);

create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id; #创建触发器，新增学员班级人数增 1

insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录

select count from class ; #查询 class 表人数

create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; #创建触发器，删除学员班级人数减 1

触发器包含多条执行语句

CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW
BEGIN
trigger_STMT
END

在上述语句中，比“只有一条执行语句的触发器”语法多出来两个关键字 BEGIN 和 END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。

在 MySQL 中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字 DELIMITER 语句。

例如，“DELIMITER ##”。可以将结束符号设置成“##”。

use school; #选择数据库 school
#创建成绩表 grade:
 create table grade(id int UNIQUE AUTO_INCREMENT, 
                    math tinyint unsigned, 
                    chinese tinyint unsigned, english tinyint unsigned
                   ); 

insert into grade values(1, 80, 87, 91),(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89); #插入多条记录

DELIMITER $$

create trigger tri_delete_student after delete on student for each row
BEGIN
Delete from grade where id = OLD.id; #删除成绩表中的记录
update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录
END;
$$

DELIMITER ;

 

三、查看触发器

（一）SHOW TRIGGERS 语句查看触发器

那么如何查看 MySQL 软件中已经存在的触发器呢？在 MySQL 软件中查看已经存在的触发器，通过 SQL 语句SHOW TRIGGERS 来实现，其语法形式如下，执行上面的 SQL 语句，执行结果如图 9-10 所示。

SHOW TRIGGERS ;

通过下图的执行结果可以发现，执行完“SHOW TRIGGERS”语句后会显示一个列表，在该列表中会显示出所有触发器的信息。其中，参数 Trigger 表示触发器的名称；参数 Event 表示触发器的激发事件；参数 Table 表示触发器对象触发事件所操作的表；参数 Statement 表示触发器激活时所执行的语句；参数 Timing 表示触发器所执行的时间

（二）查看系统表 triggers 实现查看触发器

在 MySQL 中，在系统数据库 information_schema 中存在一个存储所有触发器信息的系统表 triggers，因此查询该表格的记录也可以实现查看触发器功能。系统表 triggers 的表结构

use information_schema; #选择数据库 information_schema
select * from triggers;
select * from triggers where trigger_name=’tri_delete_student’; # 查 询 系 统 表triggers 中的触发器

 

四、删除触发器

在 MySQL 软件中，可以通过 DROP TRIGGER 语句或通过工具来删除触发器。删除触发器可以通过 SQL 语句 DROP TRIGGER 来实现，其语法形式如下：

DROP TRIGGER trigger_name;

在上述语句中，参数 trigger_name 表示所要删除的触发器名称。

注意：触发器不能修改，只能删除
 

五、触发器类型NEW和OLD的使用

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

语法：old/new.字段名

需要注意的是，old 和 new 不是所有触发器都有

触发器类型	new和old的使用
INSERT型触发器	没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器	既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器	没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

create database if not exists mydb01_trigger;

use mydb01_trigger;

-- 用户表

create table if not exists user(
 uid int primary key auto_increment,
 username varchar(50) not null,
 password varchar(50) not null
)default charset=utf8;

-- 用户信息操作日志表

create table if not exists user_logs(
 id int primary key auto_increment,
 time timestamp,
 log_text varchar(255)
 )default charset=utf8;

-- 需求1:当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器： trigger_test1
create trigger trigger_test1 after insert on user for each row
insert into user_logs values(NULL,now(),'new');

-- 在user表添加数据，让触发器自动执行
insert into user values(3,'zbb','123456');


 -- NEW和OLD
 -- insert 触发器
 -- NEW
 -- 定义触发器： trigger_test2
drop trigger trigger_test1
create trigger trigger_test2 after insert on user for each row
insert into user_logs values(NULL,now(),concat('有新用户添加,信息为：',NEW.username,NEW.password));

insert into user values(4,'abb','123456');

 -- update 触发器
 -- NEW
 -- 定义触发器： trigger_test3
 -- OLD
drop trigger trigger_test2
create trigger trigger_test3 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改,旧数据是：',OLD.uid,OLD.username,OLD.password));

update user set password = '00000' where uid=3;

 -- NEW
 drop trigger trigger_test3
create trigger trigger_test4 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改：新数据是',NEW.uid,NEW.username,NEW.password));

update user set password = '666666' where uid=3;

-- delete类型触发器
-- OLD
create trigger trigger_test5 after delete on user for each row
insert into user_logs values(NULL,now(),concat('有用户被删除，删除信息为：',OLD.uid,OLD.username,OLD.password));

delete from user where uid=3;

---

参考文章：MySQL的触发器