MySQL笔记9

触发器，事件

是一种特殊的存储过程
MySQL不支持check

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

CREATE TRIGGER trigger_name #触发器名
	trigger_time #触发时机:before|after
	trigger_event #触发条件:insert|update|delete
	ON tbl_name #表名
	FOR EACH ROW #对每一行
	trigger_body #执行的操作

利用触发器实现检查约束check
对于下表创建一个触发器,规定年龄不能低于0岁且不能高于100岁，低于0岁的用0代替，高于100岁的用100代替。

create table test1(
id int primary key auto_increment
age decimal(3,0) 
	# decimal是一种浮点型，可规定小数位数
)engine innodb default charset utf8; 

create trigger tir_test1_insert
before insert on test1 for each row
begin
	if new.age < 0 then set new.age = 0;
	elseif new.age >100 then set new.age = 100;
	end if;
end;

对象new和old

数据库一共有六种触发器，分别为：

	before insert,after insert
	before update,after update
	before delete,after delete

数据库提供了两个对象new和old分别记录新值和旧值，例如：update更新数据时会把新的数据覆盖之前的数据，那new对象就是保存的新数据，而old对象是保存被覆盖之前的数据。那么相对于insert而言是没有old的，相对于delete而言是没有new的。
当插入时间小时为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’)判断日期为某天或某年某月进行插入。
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;  

查看触发器
Mysql触发器都是存在information_shema.triggers表中的，所以查看触发器需要查询information_shema数据库中的trigger表。

show databases;
use information_shema;
show tables;
select * from triggers;# 查看所有触发器
select * from triggers
	where trigger_name='触发器名';

使用触发器制作日志

create table review
(
id int primary key auto_increment,
username varchar(64),
action_name varchar(20),
actiom_time datetime
)engine innodb default charset utf8;

create trigger test_review_insert
after insert
on test1 for each row
begin
	insert into review
		values(null,user(),'insert',now());
	end;

触发器的使用限制
不是任何语句都可以用触发器来执行的，如：插入数据完成后，能不能在触发器中写select语句查询数据呢，能不能在触发器中call存储过程或者函数调用呢？
①触发器不能使用将数据返回的存储过程和函数，也不能使用select和call的动态sql。
②触发器中不能使用开始和结束事务。
③触发器不能写得太复杂，否则没改变一行，所执行的任务就太多了。

利用错误突破使用限制
虽然触发器中不能使用select查询数据，不能直接通过参数返回,但是可以通过用户变量带回数据。
例如：创建触发器带回一个参数

create trigger test_review_id
after insert on test1 for each row
begin
	select new.age from test1
	where new.id = id into @ages;
end;

select @ids;

另外，触发器中只要有一条语句出现错误，整个触发器就不会执行。
如：

create trigger test_review_idname
after insert on test1 for each row
begin
	select new.id from test1 into @ids;
	select new.action_name from test into @actions;
	select '错误';#这里的@ids和@actions值会是什么？
end;
	select @ids;	#这里的@ids和@actions值没有被改变
	select @actions; #这就是事务处理，利用错误实现回滚（撤回）

必须带出new，否则（有时old为空）会出错.要加where限定条件，否则插入用户变量的是一列数据，会报错