MySQL触发器和更新操作

一、触发器概念

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
触发器基本语法如下所示：

 

 

 其中：trigger_time是触发器的触发事件，可以为before（在检查约束前触发）或after（在检查约束后触发）；trigger_event是触发器的触发事件，包括insert、update和delete，需注意对同一个表相同触发时间的相同触发事件，只能定义一个触发器；可以使用old和new来引用触发器中发生变化的记录内容。
触发器SQL语法：

create trigger triggerName 
after/before insert/update/delete on 表名 
for each row #这句话在mysql是固定的 
begin 
 sql语句; 
end; 

 

 推荐大家阅读：http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html （MySQL触发器（trigger）

同时使用Navicat for MySQL创建触发器方法如下图所示，但是建议使用语句操作。
首先在Navicat for MySQL找到需要建立触发器对应的表，右键“设计表”，然后创建触发器。

 

二、简单的Insert触发器

 假设存在一张学生表（student），包括学生的基本信息，学号（stuid）为主键。

 

另外存在一张成绩表（cj），对应每个学生包括一个值。其中number表示序号为主键，自动递增序列。它在插入过程中默认自增。同时假设成绩表中包括学生姓名和学号。

 

该成绩表目前没有值，先需要设计一个触发器，当增加新的学生时，需要在成绩表中插入对应的学生信息，至于具体math、chinese、english后面由老师打分更新即可。
那么，如何设计触发器呢？
1.首先它是一个插入Insert触发器，是建立在表student上的；
2.然后是after，插入后的事件；
3.事件内容是插入成绩表，主需要插入学生的学号和姓名，number为自增，而成绩目前不需要。
注意：new表示student中新插入的值。 

create trigger ins_stu 
after insert on student for each row 
begin 
 insert into cj ( stu_id, stu_name) 
 values( new.stuid, new.username); 
end; 

 

 创建的触发器如下图所示：

 

然后插入数据：

insert student values ('eastmount','111111','6','1991-12-05');

同时插入两个数据，触发器正确执行了~

 注意：创建触发器和表一样，建议增加判断：DROP TRIGGER IF EXISTS `ins_stu`;

三、判断值后调用触发器

这里简单讲述几个判断插入类型的触发器。
比如触发器调用，当插入时间小时为20时，对数据进行插入：

DROP TRIGGER IF EXISTS `ins_info`; 
create trigger ins_info 
after insert on nhfxelect for each row 
begin 
 if HOUR(new.RecordTime)='20' then 
 insert into nhfxbyhour (UnitDepName, UnitDepCode, ElectCost, TimeJG, RecordTime) 
 values( '数统学院', '1', new.USERKWH, '20', new.RecordTime); 
 end if; 
end; 

 

 这个触发器中，RecordTime为datetime类型，如"2016-08-28 20:10:00"，这时hour()这个值为20才能插入；否则数据不能插入。同时可以date_format(new.RecordTime, '%Y-%m-%d')判断日期为某天或某年某月进行插入。
同时，再如更新触发器，如果设置的值为某个范围，才进行操作或性别为"男"或"女"才进行操作。

基本语法：
if 判断条件 then
sql语句;
end if;

四、Update触发器-实时更新

假设存在一个实时插入数据的服务器，例如学生的消费金额或用电量等。
StuCost：学生的用电数据，实时插入，Cost为每30秒消费金额，RecordTime为每分钟插入时间，datetime类型；
StuCostbyHour：统计学生一小时的消费金额，HourCost为金额总数，按小时统计，TimeJD时间段，1~24，对应每小时，RecordTime为统计时间。
现在需要设计一个实时更新触发器，当插入消费数据时，按小时统计学生的消费金额，同理，用电量等。

DROP TRIGGER IF EXISTS `upd_info`; 
create trigger upd_info 
after insert on StuCost for each row 
begin 
 update StuCostbyHour set HourCost = HourCost + new.Cost 
 where (TimeJD = hour(new.RecordTime) + 1) and date_format(new.RecordTime, '%Y-%m-%d') = date_format(RecordTime, '%Y-%m-%d'); 
end; 

 

 SQL语句中，需要获取插入的时间，然后通过TimeJD时间段和日期RecordTime找到对应的值，然后进行累加即可。如下图所示：

 

上图左边是实时插入数据，右边是触发器更新加和。后面会介绍MySQL实时事件：http://blog.csdn.net/zlp5201/article/details/38309095 

示例trigger：

update：
drop TRIGGER if EXISTS upd_stu;
create trigger upd_stu after
update on student for each row
begin
update cj set stu_id = new.stuid;
end;

insert：
create trigger ins_stu 
after insert on student for each row 
begin 
 insert into cj ( stu_id, stu_name) 
 values( new.stuid, new.username); 
end;