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
BEGIN
UPDATE 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
BEGIN
UPDATE goods SET num=num-new.much WHERE gid=new.gid;
END
CREATE TRIGGER t3
AFTER
DELETE
ON ord
FOR EACH ROW
BEGIN
UPDATE goods SET num=num+old.much WHERE gid=old.gid;
END
CREATE TRIGGER t4
BEFORE
UPDATE
ON ord
FOR EACH ROW
BEGIN
UPDATE goods SET num=num+old.much-new.much WHERE gid = 1;
END
3、查看触发器
语法:SHOW TRIGGERS FROM [库名]
将查出来整个库下面的所有触发器:
show triggers from databaseLkm;
4、 删除触发器
语法:DROP TRIGGER [库名].[触发器名称]
drop trigger databaseLkm.t1;