数据库作业整理

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

drop trigger if exists update_emp_deptno;
delimiter //
create trigger update_emp_deptno
before update on dept
for each row
begin
update emp set deptno=new.deptno where deptno=old.deptno;
end
//
delimiter ;
update dept
 set deptno=6
 where deptno=1;

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

select sumsalary from dept where deptno=1;
drop trigger if exists update_deptno_sal;
delimiter //
create trigger update_deptno_sal
before 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
//
delimiter ;
update emp
set Sal=2000
where empno=1;
select sumsalary from dept where deptno=1;

创建触发器:当emp表中删除或插入一些记录时，将每个员工所在部门的最新人数写入dept表(首先向dept表添加一个person coulmn)。

select persons from dept where deptno=1;
drop trigger if exists insert_emp_person;
drop trigger if exists delete_emp_person;
delimiter //
create trigger insert_emp_person
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
//
delimiter ;

delimiter //
create trigger delete_emp_person
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
//
delimiter ;

INSERT INTO emp (empno, ename, Job, Mgr, Hiredate, Sal, Comm, deptno)
VALUES (50, 'sin', 'manager', 100, '2024-01-02 12:00:00', 50000.00, 2000.00, 1);

delete from emp where empno=50;
select persons from dept where deptno=1;