数据库—触发器(基于SQL Server)

标签: 数据库  sql  数据库

博客中用到的数据库脚本文件: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
  1. 使用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触发器可以使不能更新的视图支持更新。

版权声明:本文为sunshine543123原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sunshine543123/article/details/103897060