mysql 触发器实验

实验时，发现触发器不能返回结果集，于是需要输出时只好把想要输出的内容存进一个字符串变量里，完成操作，触动触发器后再手动select，很麻烦。

1. 在emp表（emp表）上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。

#插入信息时
set @info = ''; -- 记录输出信息
#set @count1 = 1; -- 统计输出次数
drop trigger if exists emp_insert_trigger ;
delimiter //
create trigger emp_insert_trigger after insert on emp for each row
begin
declare a int default 0; -- 暂存某部门人数
declare b decimal(7,2) default 0; -- 暂存某部门平均工资
declare c varchar(15); -- 暂存某部门名
declare isstarting boolean default true;
declare cur cursor for select count(*),avg(sal),dname from emp inner join dept on emp.deptno = dept.deptno group by emp.deptno;
declare continue handler for sqlstate '02000' set isstarting = false;
open cur;
set @info = '\n';
fetch cur into a,b,c;
while isstarting do
#set @count1 = @count1 +1;
set @info = concat(@info,'部门名：',c,' ,部门人数：',a,' ,部门平均工资：',b,'\n');
fetch cur into a,b,c;
end while;
close cur;
end//
delimiter ;
insert into emp(empno,sal,deptno) values(1111,800,10);
select @info;
#select @count1;
 
#修改信息时
set @info = '';
#set @count1 = 1; -- 统计输出次数
drop trigger if exists emp_update_trigger1 ;
delimiter //
create trigger emp_update_trigger1 after update on emp for each row
begin
declare a int default 0; -- 暂存某部门人数
declare b decimal(7,2) default 0; -- 暂存某部门平均工资
declare c varchar(15); -- 暂存某部门名
declare isstarting boolean default true;
declare cur cursor for select count(*),avg(sal),dname from emp inner join dept on emp.deptno = dept.deptno group by emp.deptno;
declare continue handler for sqlstate '02000' set isstarting = false;
open cur;
set @info = '\n';
fetch cur into a,b,c;
while isstarting do
#set @count1 = @count1 +1;
set @info = concat(@info,'部门名：',c,' ,部门人数：',a,' ,部门平均工资：',b,'\n');
fetch cur into a,b,c;
end while;
close cur;
end//
delimiter ;
update emp set sal = 2000 where empno = 1111;
select @info;
#select @count1;
 
#删除信息时
set @info = '';
#set @count1 = 1; -- 统计输出次数
drop trigger if exists emp_delete_trigger ;
delimiter //
create trigger emp_delete_trigger after delete on emp for each row
begin
declare a int default 0; -- 暂存某部门人数
declare b decimal(7,2) default 0; -- 暂存某部门平均工资
declare c varchar(15); -- 暂存某部门名
declare isstarting boolean default true;
declare cur cursor for select count(*),avg(sal),dname from emp inner join dept on emp.deptno = dept.deptno group by emp.deptno;
declare continue handler for sqlstate '02000' set isstarting = false;
open cur;
set @info = '\n';
fetch cur into a,b,c;
while isstarting do
#set @count1 = @count1 +1;
set @info = concat(@info,'部门名：',c,' ,部门人数：',a,' ,部门平均工资：',b,'\n');
fetch cur into a,b,c;
end while;
close cur;
end//
delimiter ;
delete from emp where empno = 1111;
select @info;
#select @count1;

2. 监控用户对XS表(XS表)的操作，要求：当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间。

drop trigger if exists xs_insert_trigger ;
delimiter //
create trigger xs_insert_trigger after insert on xs for each row
begin
insert into sql_info(operation,time) values('insert',curtime());
end//
delimiter ;

drop trigger if exists xs_update_trigger ;
delimiter //
create trigger xs_update_trigger after update on xs for each row
begin
insert into sql_info(operation,time) values('update',curtime());
end//
delimiter ;

drop trigger if exists xs_delete_trigger ;
delimiter //
create trigger xs_delete_trigger after delete on xs for each row
begin
insert into sql_info(operation,time) values('delete',curtime());
end//
delimiter ;

insert into xs(xh,xm,xb,cssj) values(1,1,1,20181201);
update xs set xh = 2 where xh = 1;
delete from xs where xh = 2;

3. 为emp1表(emp1表)创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员工名。

set @info = ' ';
drop trigger if exists emp1_insert_trigger ;
delimiter //
create trigger emp1_insert_trigger after insert on emp1 for each row
begin
set @info = concat('新员工员工号',new.empno,'，新员工的名字',new.ename);
end//
delimiter ;
insert into emp1(empno,ename, sal) values(1,'sdff',110);
select @info;
 

drop trigger if exists emp1_update_trigger ;
delimiter //
create trigger emp1_update_trigger after update on emp1 for each row
begin
if new.sal = old.sal then
set @info = '员工工资未变';
else
set @info = concat('修改前工资：',old.sal,' ,修改后工资:',new.sal);
end if;
end//
delimiter ;
update emp1 set sal = 1100 where empno = 1 and ename = 'sdff';
select @info;
update emp1 set sal = 1100 where empno = 1 and ename = 'sdff';
select @info;
 

drop trigger if exists emp1_delete_trigger ;
delimiter //
create trigger emp1_delete_trigger after delete on emp1 for each row
begin
set @info = concat('被删除的员工号',old.empno,'，被删除的员工名',old.ename);
end//
delimiter ;
delete from emp1 where empno = 1 and ename = 'sdff';
select @info;

4. 在emp表(emp表)上创建一个触发器，保证修改员工工资时，改后的工资低于同部门的最高工资，同时低于同部门的最低工资。

drop trigger if exists emp_update_trigger ;
delimiter //
create trigger emp_update_trigger before update on emp for each row
begin
declare max_sal decimal(7,2);
declare min_sal decimal(7,2);
declare msg varchar(20);
if new.sal != old.sal then
select max(sal) from emp where emp.deptno = new.deptno into max_sal; 
select min(sal) from emp where emp.deptno = new.deptno into min_sal;
if new.sal > max_sal or new.sal < min_sal then
set new.sal = old.sal;
end if; 
end if;
end//
delimiter ;