MySQL触发器

文章目录

1. MySQL触发器

1.1. 定义

1.2. 创建触发器

1.2.1. 创建一行执行语句的触发器

1.2.2. 创建多行执行语句的触发器

1.3. 查看触发器

1.3.1. 查看所有触发器

1.3.2. 查看指定的触发器

1.4. 删除触发器

1.5. 触发器执行的顺序

1.6. NEW 和 OLD

1.6.1. 使用方式

1.6.2. 注意

MySQL触发器

定义

  • MySQL的触发器和存储过程一样,都是嵌入到MysQL中的一段程序,不过触发器不要调用,而是由事件触发的,这些事件包括insertupdatedelete语句,如果定义了触发程序,当数据执行这些语句的时候就会激发触发器执行相应的操作。

创建触发器

创建一行执行语句的触发器

  • 格式:create trigger trigger_name trigger_time trigger_event on table for each row trigger_stmt
    • trigger_name :触发器的名字
    • trigger_time: 触发器触发的时机,取值为beforeafter
      • before:表示在激发触发器的语句执行之前执行触发器的执行语句
      • after:表示在激发触发器的语句执行之后执行触发器的执行语句
    • trigger_event:触发事件,取值为insertupdatedelete
      • insert :比如Mysql中的insertreplace语句就会触发这个事件
      • update:更新某一行的数据会激发这个事件,比如update语句
      • delete:删除某一行的时候激发触发器,可能通过delete或者replace语句激发
    • table:标识建立触发器的表名,即是当那张表发生了insertupdatedelete事件激发触发器
    • trigger_stmt:触发器执行的语句
      • 执行语句中不能返回结果集
  • 实例:
    • new.age:获取新插入的字段age的值
    • new.name : 获取新插入的name字段的值
-- 创建user表
create table user(id int primary key auto_increment,name varchar(10),age int); 

-- 设置用户变量@sum,用于统计年龄
set @sum=0;  

-- 插入数据,将会在插入数据之前激发触发器,执行触发器中定义的语句,计算出插入的年龄总和赋值给@sum
insert into user(name,age) values("Jack",22),("Tom",33);

-- 创建触发器,insert事件,在user表插入数据之前执行语句,其中的new.age是获取插入的每一行的age字段的值
create trigger sum before  insert on user for each row set @sum=@sum+new.age;

创建多行执行语句的触发器

  • 格式:
delimiter //
	create trigger trigger_name trigger_time trigger_event on table for each row
		begin
			-- 语句执行列表
		end 
		//
delimiter ;
  • 实例
    • 如果修改的名字和已经存在的重复,那么就设置异常(主键冲突)取消后面的更新语句,不过这个一定要使用before的触发时间
    • new.name : 表示修改之后的name字段的值,如果没有修改,那么和之前的一样
    • old.name :表示修改之前的name字段的值
    • 触发器不能撤销后面的操作,如果想要撤销操作可以制造异常,那么后面的语句就不会执行了,如下面的程序制造了主键冲突的异常
-- 创建user表
create table user(id int primary key auto_increment,name varchar(10),age int);

-- 创建触发器
delimiter //
	create trigger testUpdate before update on user for each row 
		begin
			declare total int default 0;  -- 创建一个total变量,统计数量
			select count(*) from user where name=new.name into total;  -- 根据修改之后的name查询出数量赋值给total
			if old.name!=new.name&&total!=0  -- count!=0并且确实修改了名字表示修改的名字重复
				then set new.id=1;   -- 名字重复导致主键冲突抛出异常,这样后续的语句就不会执行了
			end if;
		end
		//
delimiter ;

查看触发器

查看所有触发器

  • show triggers [from database] : 查看当前数据的所有的触发器
    • show triggers from test \G : 查看test数据库中的所有触发器
    • show triggers \G : 有条理的显示所有触发器

查看指定的触发器

  • 如果需要查看指定的触发器,那么可以从information_schema数据库中的triggers表中查询指定的触发器
    • select * from information_schema.triggers where trigger_name="testUpdate"\G
      • where后面是条件

删除触发器

  • drop db.trigger_name : 删除指定数据库中的触发器
    • db :数据库的名字
    • trigger_name :触发器的名字

触发器执行的顺序

  • 我们建立的数据库一般都是InnoDB数据库,其上建立的表是事务性表,也就是事务安全的。这时,若SQL语句或触发器执行失败,MySQL 会回滚事务,有:
    • 如果 BEFORE 触发器执行失败,SQL 无法正确执行。
    • SQL 执行失败时,AFTER 型触发器不会触发。
    • AFTER 类型的触发器执行失败,SQL 会回滚。

NEW 和 OLD

  • INSERT 型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
  • UPDATE型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
  • DELETE型触发器中,OLD 用来表示将要或已经被删除的原数据;

使用方式

  • NEW/OLD.columName : 比如获取更新后的name字段的值
    • new.name

注意

  • OLD 是只读的,不可以使用set修改
  • new是可以修改的,我们可以在触发器中使用set语句修改

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SpringBoot整合JSR303实现参数校验

    不知不觉Spring Boot专栏文章已经写到第十四章了,无论写的好与不好,作者都在尽力写的详细,写的与其它的文章不同,每一章都不是浅尝辄止。如果前面的文章没有...

    爱撒谎的男孩
  • Servlet总结四(过滤器)

    爱撒谎的男孩
  • Mybatis之使用总结

    爱撒谎的男孩
  • 【DB笔试面试448】Oracle中有哪几类触发器?

    Oracle数据库有4种触发器,分别是DML、替代触发器(INSTEAD OF触发器)、DDL和系统触发器,一般的应用系统中都使用到DML、替代触发器,而DDL...

    小麦苗DBA宝典
  • sql server 触发器

    触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用,也不允许设置参数。它是建立在触发事件上的。 触发器可以强制执行一...

    欠扁的小篮子
  • Mysql触发器

    MySQL触发器 1、 触发器作用 日志跟踪:使用数据库记录apache等日志,可以使用自动插入操作 验证数据:触发器可以验证更新数据库要存入的数据 引用完整:...

    苦咖啡
  • Oracle触发器

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    颍川
  • 触发器最全总结(适合复习数字逻辑使用)

    cuptobjut
  • MySQL从删库到跑路_高级(五)——触发器

    触发器是和表关联的特殊的存储过程,可以在插入,删除或修改表中的数据时触发执行,比数据库本身标准的功能有更精细和更复杂的数据控制能力。

    良月柒
  • 2014/11/06 Oracle触发器初步 2014-11-06 09:03 49人阅读 评论(0)

    触发器我就不多解释了,保证数据的完整性的神器,嗯..也是减少程序员工作托管给数据库操作的好帮手.就不讲一些大道理了.通俗点,我们对数据库的操作,无非就是增 删 ...

    GuZhenYin

扫码关注云+社区

领取腾讯云代金券