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中的一段程序，不过触发器不要调用，而是由事件触发的，这些事件包括insert，update，delete语句，如果定义了触发程序，当数据执行这些语句的时候就会激发触发器执行相应的操作。

创建触发器

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

• 格式：create trigger trigger_name trigger_time trigger_event on table for each row trigger_stmt
  • trigger_name ：触发器的名字
  • trigger_time： 触发器触发的时机，取值为before，after
    • before：表示在激发触发器的语句执行之前执行触发器的执行语句
    • after：表示在激发触发器的语句执行之后执行触发器的执行语句
  • trigger_event：触发事件，取值为insert，update，delete
    • insert ：比如Mysql中的insert和replace语句就会触发这个事件
    • update：更新某一行的数据会激发这个事件，比如update语句
    • delete：删除某一行的时候激发触发器，可能通过delete或者replace语句激发
  • table：标识建立触发器的表名，即是当那张表发生了insert，update，delete事件激发触发器
  • 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语句修改