数据库原理与应用--实验十一

Judy2hu

于 2024-03-12 22:12:20 发布

阅读量497

点赞数 10

文章标签：数据库 java 开发语言

本文链接：https://blog.csdn.net/m0_71492828/article/details/136664985

版权

本文详细介绍了在SQL中创建和应用各种触发器，如级联删除、年龄限制选课、成绩验证、教师信息更新检查以及数据安全保护。通过实例演示了如何实现级联删除、年龄校验插入和成绩限制更新等操作。

摘要由CSDN通过智能技术生成

实验内容：

在表Student中建立删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除；建立完触发器后删除Student中学号为‘00030’的元组，看看表SC中SNO为‘00030’的选课记录是否也一起删除；
在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，实现规定年龄24岁以上（包括24岁）的学生才能选修职业规划这门课程，如果年龄小于24岁，则输出’年龄小于24，不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00001’,’005’,null)和(‘00005’,’005’,null)看看结果,试试使用一条SQL语句插入(‘00012’,’005’,null)和(‘00013’,’005’,null)看看结果，如果两行数据都插入不成功说明原因，并通过修改触发器实现一条SQL语句插入(‘00012’,’005’,null)和 (‘00013’,’005’,null)时，(‘00013’,’005’,null)数据插入成功；
在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能修改’，修改失败，用update语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果,试试用update语句把课程号为001的成绩都改为70，看看结果，要求触发器能够实现001号课程低于70分的成绩都能修改成为70，高于70分的成绩都不修改；
在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和工资，则输出’更新了年龄和工资’，如果更新了年龄没有更新工资，则输出’更新了年龄’，如果更新了工资而没有更新年龄，则输出’更新了工资’，创建完后使用SQL语句把tno为001的年龄加1，把tno为002的工资加1，把tno为003的年龄和工资都加1，看看结果；
在不删除触发器的前提下，使题3创建的触发器无效；
创建一个名为tri_Delete_C的触发器，要求首先判断数据库中是否已经存在名为tri_Delete_C的触发器，如果存在，首先删除，再创建，触发器要求删除一门课程时候，首先判断该课程有否有人选，如果有人选，则不能删除,并通过测试数据验证该触发器的执行情况；
在表Teacher中创建safty触发器，拒绝用户对数据库中的Teacher表进行删除和更改操作,创建完后通过修改Teacher表的结构和删除Teacher表来测试一下效果（提示：DDL触发器，关键字为DROP_TABLE , ALTER_TABLE）。

源代码：

/*1.在表Student中建立删除触发器，实现表Student和表SC的级联删除，
也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除；
建立完触发器后删除Student中学号为‘00030’的元组，看看表SC中SNO
为‘00030’的选课记录是否也一起删除*/
CREATE TRIGGER s_delete
ON student
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM SC
WHERE sno in(
     SELECT  sno
      FROM  DELETED)
DELETE FROM student
WHERE sno in(
	SELECT  sno  
	FROM  DELETED)
END
DELETE FROM STUDENT WHERE SNO='00030'
SELECT * FROM STUDENT
SELECT * FROM SC

/*2.在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在
表SC中建立一个触发器，实现规定年龄24岁以上（包括24岁）的学生才能选修
职业规划这门课程，如果年龄小于24岁，则输出’年龄小于24，不能选修该门
课程’，插入失败，用SQL语句在SC表中分别插入(‘00001’,’005’,null)和
(‘00005’,’005’,null)看看结果,试试使用一条SQL语句插入(‘00012’,
’005’,null)和(‘00013’,’005’,null)看看结果，如果两行数据都插入
不成功说明原因，并通过修改触发器实现一条SQL语句插入(‘00012’,’005’
,null)和 (‘00013’,’005’,null)时，(‘00013’,’005’,null)数据插入成功；*/
GO
CREATE TRIGGER Q2
ON SC
FOR INSERT
AS
BEGIN
DECLARE @ID CHAR(5)
SELECT @ID=SNO FROM INSERTED
IF((SELECT CNO FROM INSERTED)='005'AND
(SELECT SAGE FROM STUDENT WHERE SNO=@ID)<24)
BEGIN 
PRINT'年龄小于24，不能选修该门课程'
ROLLBACK TRANSACTION
END
ELSE
PRINT'已选修'
END
INSERT INTO COURSE VALUES('005','职业规划','4','0014')
INSERT INTO SC VALUES('00001','005',null) 
INSERT INTO SC VALUES('00005','005',null) 
SELECT * FROM COURSE
SELECT * FROM SC


/*3.在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，
如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能
修改’，修改失败，用update语句把学号为00001，课程号为001的成绩分别改为
90和70，看看结果,试试用update语句把课程号为001的成绩都改为70，看看结果，
要求触发器能够实现001号课程低于70分的成绩都能修改成为70，高于70分的成绩
都不修改*/
GO
CREATE TRIGGER Q3
ON SC  
AFTER  UPDATE
AS
IF (UPDATE(score))
BEGIN
DECLARE @score1 NUMERIC(3,1),@SCORE2 NUMERIC(3,1)
select @SCORE1 =SCORE FROM DELETED
select @SCORE2 =SCORE FROM DELETED
IF(@SCORE1>@SCORE2)
PRINT'修改成功'
ELSE 
UPDATE SC
SET SC.SCORE=@SCORE2 FROM SC,DELETED
WHERE SC.CNO=DELETED.CNO AND SC.SNO=DELETED.SNO 
PRINT'修改后的成绩比修改前低，不能修改'
END
UPDATE SC
SET SCORE=90 WHERE SNO='00001'
UPDATE SC
SET SCORE=70 WHERE CNO='001'
SELECT * FROM SC

/*4.在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和工资，
则输出’更新了年龄和工资’，如果更新了年龄没有更新工资，则输出’更新了
年龄’，如果更新了工资而没有更新年龄，则输出’更新了工资’，创建完后
使用SQL语句把tno为001的年龄加1，把tno为002的工资加1，把tno为003的年龄和
工资都加1，看看结果；*/
GO 
CREATE TRIGGER Q4
ON TEACHER
AFTER UPDATE
AS BEGIN
DECLARE @AGE INT,@SAL FLOAT
SELECT @AGE=AGE FROM DELETED
SELECT @SAL=SAL FROM DELETED
IF(@AGE<>(SELECT AGE FROM INSERTED)AND @SAL<>(SELECT SAL FROM INSERTED))
PRINT'更新了年龄和工资'
ELSE IF(@AGE<>(SELECT AGE FROM INSERTED)AND @SAL=(SELECT SAL FROM INSERTED))
PRINT'更新了工资'
ELSE IF(@AGE=(SELECT AGE FROM INSERTED)AND @SAL<>(SELECT SAL FROM INSERTED))
PRINT'更新了年龄'
END
UPDATE TEACHER SET AGE=AGE+1 WHERE TNO='0001'
UPDATE TEACHER SET SAL=SAL+1 WHERE TNO='0002'
UPDATE TEACHER SET AGE=AGE+1 WHERE TNO='0003'
UPDATE TEACHER SET SAL=SAL+1 WHERE TNO='0003'
SELECT * FROM TEACHER
/*5.在不删除触发器的前提下，使题3创建的触发器无效*/
GO
ALTER TABLE SC DISABLE TRIGGER Q3

/*6.创建一个名为tri_Delete_C的触发器，要求首先判断数据库中是否已经
存在名为tri_Delete_C的触发器，如果存在，首先删除，再创建，触发器要求
删除一门课程时候，首先判断该课程有否有人选，如果有人选，则不能删除,
并通过测试数据验证该触发器的执行情况；*/

IF(EXISTS(SELECT * FROM SYSOBJECTS WHERE xtype='tr'
AND NAME='tri_Delete_C'))
BEGIN
DROP TRIGGER tri_Delete_C
PRINT'已删除'
end
GO
CREATE TRIGGER tri_Delete_C ON COURSE
FOR DELETE 
AS BEGIN
IF EXISTS(SELECT* FROM SC INNER JOIN DELETED ON SC.CNO=DELETED.CNO)
BEGIN
PRINT'不能删除'
ROLLBACK TRANSACTION
END
END

/*7.在表Teacher中创建safty触发器，拒绝用户对数据库中的Teacher表进行删除
和更改操作,创建完后通过修改Teacher表的结构和删除Teacher表来测试一下效果*/
CREATE TRIGGER safty
ON Teacher
for delete,update
AS
BEGIN
    RAISERROR('表Teacher禁止删除和修改', 16, 1);
    ROLLBACK TRANSACTION;
END;
ALTER TABLE teacher ENABLE TRIGGER safty
delete Teacher;
update Teacher
set age=age+1