【mysql】E4 触发器的定义和应用

E4 触发器的定义和应用
一、实验目的:
熟练使用MySQL触发器的定义和应用。
二、实验要求:
1、基本硬件配置:英特尔Pentium III 以上,大于4G内存；
2、软件要求:Mysql；
3、时间:1小时；
4、撰写实验报告并按时提交。
三、实验内容:
问题1：创建触发器:当从emp表中删除一些记录时，将删除的记录写入员工历史表(首先创建历史表)。

use example
create table emp_his as select * from emp where 1 = 2;

delimiter //
create trigger tr_del_emp
after delete
on emp
for each row
begin
insert into emp_his(deptno, empno, ename, job, mgr, sal, comm, hiredate)
values(old.deptno, old.empno, old.ename, old.job, old.mgr, old.sal, old.comm, old.hiredate);
end //

Select * from emp_his;

问题2：设计一个行触发器:当DEPT表中deptno列的值被更新时，EMP表中相应的deptno值也被修改。

Delimiter //
create trigger tr_update_emp                                 
after update                                                 
on dept                                                      
for each row                                                 
begin                                                        
update emp set deptno = new.deptno where deptno = old.deptno;
end //                                                       

直接进行更新会遇到这么一个报错

Cannot delete or update a parent row: a foreign key constraint fails

因为有外键约束,所以不能直接update,所以我们先把外键约束检查关闭

Set foreign_key_checks = 0;

update dept set deptno = 6 where deptno = 1;

select * from emp;

执行之后可见成功了,原本的deptno从1变成了6
tips:这里注意,考虑到主键唯一,所以不要设置重复的deptno,要不然会报错

问题3：设计一个行触发器用于emp表的更新，当修改员工的工资sal时候触发更新所在部门的工资总和(首先在dept表中添加一个sumsalary coulmn)。

create trigger tri_update_emp                                                  
after update                                                                   
on emp                                                                         
for each row                                                                   
begin                                                                          
update dept set sumsalary = (select sum(sal) from emp where deptno = new.deptno) where deptno = new.deptno;
end //

update emp set sal = 12345 where empno = 2;

此处被报错,因为之前我们创建过一个emp表的update触发器,不删除的话就会连环触发,所以我们先把之前那个触发器删除掉.

---

tips:如果是用cmd直接drop则没有什么问题,但是如果是用navicat等工可视化工具删除触发器的时候不要忘了保存!!

---

select * from dept;

问题4：创建触发器:当emp表中删除或插入一些记录时，将每个员工所在部门的最新人数写入dept表(首先向dept表添加一个person coulmn)。
同样,我们首先要把之前创建的delete触发器给删除.

delimiter //
create trigger tr_delete_emp                                                  
after delete                                                                  
on emp                                                                        
for each row                                                                  
begin                                                                         
update dept set persons = (select count(*) from emp where deptno = old.deptno) where deptno = old.deptno;
End //

delete from emp where empno = 5;
select * from dept;

delimiter //                                                                  
create trigger tr_insert_emp                                                  
after insert                                                                  
on emp                                                                        
for each row                                                                  
begin                                                                         
update dept set persons = (select count(*) from emp where deptno = new.deptno) where deptno = new.deptno;
End //

insert into emp values(23, 'laurie', 'Manager', null, '2022-04-14', 45236.0, 50
0, 3);
Select * from dept;

新手上路,有错请指正;