一、创建触发器
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
--创建只有一个执行语句的触发器--创建触发器的语法:
CREATE TRIGGERtrigger_name trigger_time trigger_eventON table_name FOR EACH ROW trigger_stmt
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `mysql_test`.`tri_01` BEFORE/AFTER INSERT/UPDATE/DELETE
ON`mysql_test`.`student`FOREACH ROWBEGINsql语句END$$
DELIMITER ;
trigger_time:触发时机, before(在检查约束前触发)或after(在检查约束后触发)
trigger_event:触发事件 INSERT、UPDATE、DELETE、
table_name:建立触发器的表明,即在那张表上创建触发器
trigger_stmt:触发器执行语句
MySQL的触发器和存储过程一样,都是嵌入到MySQL的一段程勋,触发器是由事假来触发某个操作,这些事件包括INSERT、UPDATE、DELETE语句。
1.1、简单的Insert触发器
假设存在一张学生表(student),包括学生的基本信息,学号(sid)为主键。
CREATE TABLE`student` (
`sid`int(8) NOT NULLAUTO_INCREMENT,
`sname`varchar(10) NOT NULL,PRIMARY KEY(`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=20200105 DEFAULT CHARSET=utf8
另外存在一张成绩表(score_sheet),对应每个学生包括一个值。其中number表示序号为主键,自动递增序列。它在插入过程中默认自增。同时假设成绩表中包括学生姓名和学号。
CREATE TABLE`score_sheet` (
`id`int(20) NOT NULL AUTO_INCREMENT COMMENT '成绩表id,主键自增',
`stu_id`int(8) NOT NULL COMMENT '学生表id',
`stu_name`varchar(10) NOT NULL COMMENT '学生名称',
`english`double DEFAULT NULL COMMENT '英语成绩',
`chinese`double DEFAULT NULL COMMENT '语文成绩',
`match`double DEFAULT NULL COMMENT '数学成绩',PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
该成绩表目前没有值,先需要设计一个触发器,当增加新的学生时,需要在成绩表中插入对应的学生信息,至于具体math、chinese、english后面由老师打分更新即可。那么,如何设计触发器呢?
首先它是一个插入Insert触发器,是建立在表student上的;
然后是after,插入后的事件;
事件内容是插入成绩表,主需要插入学生的学号和姓名,number为自增,而成绩目前不需要。
注意:new表示student中新插入的值。
--创建触发器
DELIMITER $CREATE TRIGGERins_stu
AFTERINSERT ON student FOREACH ROWBEGIN
INSERT INTO score_sheet (stu_id, stu_name) VALUES( NEW.sid, NEW.sname);END$
DELIMITER;--给学生表插入一条记录
INSERT INTO student (sname)VALUES ('张三');--可以看到成绩表同时插入一条记录
SELECT * FROMstudentSELECT * FROM score_sheet
1.2、判断值后调用触发器
这里简单讲述几个判断插入类型的触发器。比如触发器调用,当插入时间小时为20时,对数据进行插入:
DELIMITER $create triggerins_info
afterinsert on nhfxelect foreach rowbegin
if HOUR(new.RecordTime)='20' then
insert intonhfxbyhour (UnitDepName, UnitDepCode, ElectCost, TimeJG, RecordTime)values( '数统学院', '1', new.USERKWH, '20', new.RecordTime);end if;END$
DELIMITER;
这个触发器中,RecordTime为datetime类型,如“2016-08-28 20:10:00”,这时hour()这个值为20才能插入;否则数据不能插入。同时可以date_format(new.RecordTime, ‘%Y-%m-%d’)判断日期为某天或某年某月进行插入。 同时,再如更新触发器,如果设置的值为某个范围,才进行操作或性别为“男”或“女”才进行操作。
# 基本语法:if 判断条件 thensql语句;end if;
1.3、Update触发器-实时更新
假设存在一个实时插入数据的服务器,例如学生的消费金额或用电量等。StuCost:学生的用电数据,实时插入,Cost为每30秒消费金额,RecordTime为每分钟插入时间,datetime类型;StuCostbyHour:统计学生一小时的消费金额,HourCost为金额总数,按小时统计,TimeJD时间段,1~24,对应每小时,RecordTime为统计时间。现在需要设计一个实时更新触发器,当插入消费数据时,按小时统计学生的消费金额,同理,用电量等。
DROP TRIGGER IF EXISTS`upd_info`;create triggerupd_info
afterinsert on StuCost foreach rowbegin
update StuCostbyHour set HourCost = HourCost +new.Costwhere (TimeJD = hour(new.RecordTime) + 1) and date_format(new.RecordTime, '%Y-%m-%d') = date_format(RecordTime, '%Y-%m-%d');end;
二、查看触发器
SHOW TRIGGERS;
在triggers表中查看触发器信息
在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中,可以通过select来查看,语法:SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition
三、删除触发器
DROP TRIGGER [scheme_name] trigger_name
scheme_name:表示数据库名称,是可选的。如果省略了scheme_name,将从当前数据库中舍弃触发程序;