博客中用到的数据库脚本文件:https://download.csdn.net/download/sunshine543123/12087175
1.(此题为后续实验题准备基础表和数据)创建一张Total_Hours表,用来保存每个员工所有项目总的工作时间,包含员工SSN和总工作时间(totalHours)两列;然后将employee表的所有员工SSN和初始工作时间(0)插入到表Total_Hours中;最后用从works_on表统计的每个员工所有项目总的实际工作时间更新表Total_Hours,如下图所示:
create table Total_hours (ssn varchar(50) primary key,totalhours decimal(18,1) default(0))
go
insert into dbo.Total_hours select ssn,0 from Employee
go
update Total_hours set totalhours=
(Select SUM(hours) from WORKS_ON where Total_hours.ssn=WORKS_ON.ESSN)
go
select *from Total_hours
select *from WORKS_ON
2.使用INSERT触发器实现:①每向employee表插入一个员工时,自动向Total_Hours表插入这个员工的SSN并将其初始totalHours置为0;②每向works_on表插入一行数据时,自动更新Total_Hours表中该员工对应的totalHours。(测试要求:要求在插入语句前后都加上select * from Total_Hours以查看触发器导致的Total_Hours表的变化)
select * from Total_Hours
go
create trigger T_employ on Employee for insert
as
begin
insert Total_Hours select SSN,0 from inserted
end
go
insert into Employee values('111','A','111','201700000000','1998-10-04','china','F',100000,'333445555',5,1)
select * from