实验二:数据库的安全和完整性约束

发布网友发布时间：2022-04-25 14:38

共1个回答

热心网友时间：2022-04-08 02:03

（1）create trigger Upd_Credit on SC for insert
as
Declare @SNO varchar(100),@CNO varchar(100),@GRADE int,@NoPass int ,@CREDIT int
select @SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=(case when GRADE<60 then 1 else 0 end) from inserted
select @CREDIT=CREDIT from Courses where CNO=@CNO
update Credits
set SumCredit=SumCredit+@CREDIT,
NoPass=NoPass+@NoPass
where SNO =@SNO

（2）create trigger Upd_StuView on Student_Grade for insert
as
declare @SNAME varchar(100),@CNAME varchar(100),@GRADE int
select @SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADE from inserted
IF(EXISTS(SELECT * FROM Students WHERE SNAME=@SNAME) AND
EXISTS (SELECT * FROM Courses WHERE CNAME=@CNAME))
BEGIN
Insert into SC
select (SELECT TOP 1 SNO FROM Students WHERE SNAME=@SNAME),
(SELECT TOP 1 CNO FROM Courses WHERE CNAME=@CNAME),
@GRADE
END