当前位置：首页 > article >正文

MySql触发器介绍及应用

article 2025/9/14 14:49:23

1、触发器（trigger）的定义

触发器是与表事件相关的特殊存储过程，它的执行不由程序调用，也非手工启动，而是由事件触发、激活从而实现执行。

简单来说，触发器就是事先为某张表绑定一段代码，当表中的某些内容发生增、删、改时，系统会自动触发代码并执行。

存储过程: 存储过程则需要主动调用其名字执行。

注意：触发器由事件触发执行，存储过程主动调用。

2、触发器的作用：

1、安全性，可以基于数据库的值使用户具有操作数据库的某种权利。
2、审计，可以跟踪用户对数据库的操作。
3、实现复杂的非标准的数据库相关完整性规则，触发器可以对数据库中相关的表进行连环更新。
4、触发器执行错误，前面成功执行的SQL将被撤销。
5、同步实时地复制表中的数据
6、自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

讲解触发器的经典案例，就是下订单，比如存货100件，如果下订单购买10件，该商品的库存量需相应减少，即买几个商品就减少多少个库存量，先看建表语句:

create table goods(gid int,name varchar(20),num smallint
);
create table ord(oid int,gid int,much smallint
);
insert into goods values(1,'cat',100);
insert into goods values(2,'dog',200);
insert into goods values(3,'pig',300);

创建触发器语句如下:


DROP TRIGGER IF EXISTS databaseName.tri_Name;  
CREATE TRIGGER tri_Name  //tri_Name代表触发器名称
tirgger_time trigger_event on tableName  
// tirgger_time为触发时机，可选值有after/before
//trigger_event为触发事件，可选值有insert/update/delete
FOR EACH ROW   // 表示任何一条记录上的操作满足触发事件都会触发该触发器。 
BEGIN  sql语句;  
END

CREATE TRIGGER t1 
AFTER
INSERT
ON ord
FOR EACH ROW
BEGINUPDATE goods SET num=num-2 WHERE gid = 1;
END

我们现在已经建立了一个简单的存储过程，只要订单表ord的gid=1的商品有INSERT(只要买了猫cat，猫的存货就减少2，当然，实际开发过程中减去的具体值，需要是订单购买数量值)，看如下过程:
在这里插入图片描述

我们执行一下插入操作：INSERT INTO ord VALUE(1,10,66)
在这里插入图片描述
创建触发器的语法

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器名称
tirgger_time：触发执行事件BEFORE：事件之前触发AFTER：事件之后触发
trigger_event：触发事件INSERT：插入某一行时激活触发器，INSERT,LOAD DATA,REPLACE语句可以触发UPDATE：更改某一行时激活触发器，UPDATE语句可以触发DELETE：删除某一行时激活触发器，DELETE,REPLACE语句可以触发
tb_name：触发器要执行的哪张表
FOR EACH ROW：触发频率为每一行触发一次
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

触发器创建的四个要素
1)监视地点（table）
2)监视事件（insert/update/delete）
3)触发时间（after/before）
4)触发事件（insert/update/delete）

监视谁：ord（订单表）
监视事件：insert（ord表插入操作）
触发时间：after（在ord插入操作后触发）
触发事件：update（触发更新操作 goods表更新）

触发器中引用行变量
1)在触发目标上执行insert操作后会有一个新行，如果在触发事件中需要用到该新行的变量，可以用new关键字表示
2)在触发目标上执行delete操作后会有一个旧行，如果在触发事件中需要用到该旧行的变量，可以用old关键字表示
3)在触发目标上执行update操作后原纪录是旧行，新记录是新行，可以使用new和old关键字来分别操作

CREATE TRIGGER t2
AFTER
INSERT
ON ord
FOR EACH ROW
BEGINUPDATE goods SET num=num-new.much WHERE gid=new.gid;
ENDCREATE TRIGGER t3
AFTER
DELETE
ON ord
FOR EACH ROW
BEGINUPDATE goods SET num=num+old.much WHERE gid=old.gid;
ENDCREATE TRIGGER t4
BEFORE
UPDATE
ON ord
FOR EACH ROW
BEGINUPDATE goods SET num=num+old.much-new.much WHERE gid = 1;
END