数据库—触发器（基于SQL Server）

博客中用到的数据库脚本文件：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 Total_Hours
go

create trigger T_works on Works_on for insert
as
begin
update Total_Hours set totalHours=
(select sum(hours) from Works_on where Total_Hours.SSN=WORKS_ON.ESSN) from inserted
end
go
insert into WORKS_ON values('201700000000','1','10.0')

select * from Total_Hours

3．使用UPDATE触发器实现每当更新works_on表HOURS数据时（每次只更新一行数据），自动更新Total_Hours表的totalHours.
（测试要求：要求在update语句前后都加上select * from Total_Hours以查看触发器导致的Total_Hours表的变化）

create trigger T_Work on WORKS_ON for update
as
begin
declare @ssn varchar(20),@new_hours numeric(18, 1)
select @ssn=ESSN from deleted
select @new_hours=SUM(HOURS) from WORKS_ON where ESSN=@ssn
update Total_Hours set totalHours=@new_hours where SSN=@ssn
end
go

update WORKS_ON set HOURS=20.0 where ESSN='201700000000'

select * from Total_Hours

4. 使用UPDATE触发器实现：当且仅当修改EMPLOYEE表的SALARY数据时（每次只更新一行数据），如果修改后的SALARY小于等于修改以前的SALARY，则不允许修改，并提示“修改后工资小于等于修改前工资，修改失败！”，否则提示“修改成功！”。

create trigger T_Salary on Employee for update
as
begin
declare @old_Salary int,@new_Salary int,@ssn varchar(20)
select @new_Salary=SALARY,@ssn=SSN from inserted
select @old_Salary=SALARY from deleted
if update(SALARY) begin
   if(@new_Salary>@old_Salary)
       print '修改成功！'
   else begin
       print '修改后工资小于等于修改前工资，修改失败'
       update Employee set SALARY=@old_Salary where SSN=@ssn
   end
end
end

update Employee set SALARY=20000 where SSN='123456789'
update Employee set SALARY=60000 where SSN='123456789'

5．使用DELETE触发器（提示：用INSTEAD
OF触发器）实现每当删除employee表员工（有可能会同时删除多个员工，需要用游标处理）时，如果被删除的员工是普通员工，则同时级联删除Total_Hours表、works_on表、Dependent表中与被删除员工相关的所有数据，并将employee表和works_on表中被删除数据分别备份到employee_backup表和works_on_backup表中；如果被删除员工是部门经理，则不允许删除，并提示“Manager can not be deleted!”。（提示：employee 和其它表之间不存在任何外键约束）（测试要求：分别删除部门5一个部门经理和一个非部门经理；同时删除所有部门4员工，观察三种情况执行结果）

select * into employee_backup from Employee where 1<>1
select * into works_on_backup from works_on where 1<>1
go

create trigger T_DelEmploy
on employee
instead of delete
as
begin
declare @ssn varchar(20),@count int
declare employ_cursor CURSOR
for
select SSN from deleted
open employ_cursor
fetch next from employ_cursor into @ssn
while @@FETCH_STATUS=0
begin
select @count=count(*) from DEPARTMENT where MGRSSN=@ssn
if(@count!=0)
   print 'Manager can not be deleted!'   
else begin
   insert into works_on_backup select *from WORKS_ON where ESSN=@ssn
   insert into Employee_backup select * from employee where SSN=@ssn
   delete Employee where ssn=@ssn
   delete Total_Hours where SSN=@ssn
   delete works_on where ESSN=@ssn
   delete Dependent where ESSN=@ssn
end
fetch next from employ_cursor into @ssn
end
close employ_cursor
deallocate employ_cursor
end

delete Employee where ssn='888665555'
delete Employee where ssn='201700000000'
delete Employee where DNO=4
    
select * from works_on_backup
select * from employee_backup

6、检查并修改EMPLOYEE表和DEPARTMENT表的设计（除主键外，其他所有列都可为空）。按以下SQL语句创建一个EMP_DEPT 视图。
CREATE
VIEW EMP_DEPT
AS
SELECT fname,lname,ssn,dnumber,dname from
employee e join department d on e.dno=d.dnumber
用INSERT语句向该视图中插入一行数据，姓名为你的姓名全拼，SSN为你的学号，部门编号为6，部门名称为“COMPUTER”。请问该视图是否可插入数据？为什么？

利用INSTEAD
OF触发器更新该视图，即执行了视图插入语句后，查询该视图能看到和你姓名相关的记录。

CREATE VIEW EMP_DEPT
AS
SELECT fname,lname,ssn,dnumber,dname from 
employee e join department d
on e.dno=d.dnumber
go

insert EMP_DEPT values('111','111','201700000000',6,'COMPUTER')
select *from EMP_DEPT
go

create trigger view_emp
on EMP_DEPT instead of insert
as
begin
declare @fn varchar(20),@ln varchar(20),@sn varchar(20),@dnu int,@dn varchar(20)
select @fn=fname,@ln=lname,@sn=ssn,@dnu=dnumber,@dn=dname from inserted
insert into Employee(FNAME,LNAME,SSN,DNO) values(@fn,@ln,@sn,@dnu)
insert into Department(MGRSSN,DNUMBER,DNAME)values(@sn,@dnu,@dn)
end

视图由多张基表构成时不能更新。
INSTEAD OF触发器可以使不能更新的视图支持更新。