数据库实验——触发器及数据库安全性

📧 触发器及数据库安全性
提示：本文的大概内容：本实验是清华大学出版社《数据库原理与技术》李春葆 1、P325第7题至第10题。2、P396上机实验题8各题目。3、P396上机实验题9各题目。

---

前言

😁老话重提：基本原理一定要去翻阅书籍，学计算机的亲们，一定要多动手去打代码，才能去理解他的含义和内容，这对培养你的编程能力和编程思维非常重要！！！

---

提示：由于博主仍是本科小菜鸟，若存在错误，请各位同学指正，相关代码仅供参考。

题目与代码

7.在 school 数据库的 score 表上创建一个 INSERT 触发器,规定插人记录的课程号只能来自 course 表。

--p325-7
USE School
GO
CREATE TRIGGER trig1
	ON dbo.Score AFTER INSERT
AS
	IF NOT EXISTS(SELECT C.课程号 FROM dbo.Course C
	WHERE 课程号=(SELECT Ins.课程号 FROM Inserted Ins))
	BEGIN
		RAISERROR('插入记录的课程号只能来自course表',16,1)
		ROLLBACK 
	END
GO

8.在 school 数据库的 score 表上创建一个 UPDATE 触发器,规定修改记录的课程号只能来自 course 表。

--p325-8
USE School
GO
CREATE TRIGGER trig2
	ON dbo.Score AFTER UPDATE
AS
	IF NOT EXISTS(SELECT C.课程号 FROM dbo.Course C
	WHERE 课程号=(SELECT Ins.课程号 FROM Inserted Ins))
	BEGIN
		RAISERROR('修改记录的课程号只能来自course表',16,1)
		ROLLBACK 
	END
GO

9.在 school 数据库的 score 表上创建一个 UPDATE 触发器,规定修改记录的分数只能在 1～100 范围内。

--p325-9
CREATE TRIGGER trig3		
	ON dbo.Score AFTER UPDATE
AS
	DECLARE @grade FLOAT
	SELECT @grade=分数 FROM inserted
	IF (@grade <1 OR  @grade >100)
	BEGIN 
		RAISERROR('Error',16,1)
		ROLLBACK 
	END 

10.在 school 数据库的 teacher 表上创建一个 DELETE 触发器,规定不能删除任课教师的记录。

--p325-10
CREATE TRIGGER trig4		
	ON dbo.Teacher AFTER DELETE
AS
	BEGIN 
		IF((EXISTS( SELECT * FROM deleted)) 
		OR (EXISTS( SELECT * FROM inserted)))
		PRINT'不能删除任课教师的记录'
		ROLLBACK
	END

(1)在 borrow 上建立一个触发器，完成以下功能：如果读者借阅的书名是“网络工程”，就将该借书记录保存在 borrow1表中(borrow1表的结构与 borrow 相同)。

USE Library2143		--首先创建borrow1表
CREATE TABLE borrow1
(	
	学号 NVARCHAR(10),
	图书编号 NVARCHAR(10),
	借书日期 DATE
)
--test8-1
CREATE TRIGGER trigbor
	ON dbo.borrow2143 AFTER INSERT,UPDATE
AS
	INSERT dbo.borrow1
	SELECT Ins.*
	FROM Inserted Ins,dbo.book2143 B
	WHERE Ins.图书编号=B.图书编号 AND B.图书名='网络工程'
GO

(2)在 borrow 上建立一个触发器,完成以下功能：当删除 borrow 表中的任何记录时，将该记录保存在 borrow1表中(borrow1表的结构和 borrow 相同)。

--test8-2
USE Library2143
GO
CREATE TRIGGER trigbor1
	ON dbo.borrow2143 AFTER DELETE
AS 
	INSERT dbo.borrow1
	SELECT * FROM Deleted
GO

(3)删除前面创建的触发器。

--test8-3
USE Library2143
DROP TRIGGER trigbor,trigbor1

(1)创建一个登录账号 Liblog，其密码为“123456”。

--test9-1
CREATE LOGIN Liblog WITH PASSWORD='123456'

(2)为 Liblog 登录账号在 Library 数据库中创建一个数据库用户账号 Liblog。

提示：（这里我想说一下，题目应该是有误的，数据库用户账号应该为libuser，因为后面的题会继续用到，然而书上p337说每个登录账号在一个数据库里只能有一个用户账号，这就和书上说的冲突了，我也亲自试了一下，并不能同时建两个用户名）

--test9-2
USE Library2143
GO
CREATE USER libuser FOR LOGIN Liblog	--建立用户 libuser

(3)将 Library 数据库中建表的权限授子 libuser 数据库用户账号，然后收回该权限。

--test9-3
USE Library2143
GO
GRANT CREATE TABLE TO libuser	--授予用户libuser建表权限
REVOKE CREATE TABLE TO libuser	--撤销用户libuser建表权限

(4)将 Library 数据库中表 student 上的 INSERT、UPDATE 和 DELETE 权限授予libuser 数据库用户账号，然后收回该权限。

--test9-4
USE Library2143
GO
GRANT INSERT,UPDATE,DELETE ON dbo.student2143 TO libuser	--授予libuser用户 对表student2143的 INSERT,UPDATE,DELETE 权限
DENY INSERT,UPDATE,DELETE ON dbo.student2143 TO libuser		--禁止libuser用户 对表student2143的 INSERT,UPDATE,DELETE 权限

(5)删除前面创建的登录账号 Liblog 和数据库用户账号 libuser.

--test9-5
USE Library2143
GO
DROP LOGIN Liblog 
DROP USER libuser
GO

总结

相关的测试数据我没贴出来，同学们可以根据自己想试的数据进行测试。对此次知识点进行一个小总结，触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。它可以强制用比CHECK约束定义的约束更为复杂的约束。以下引用自隔壁大佬的笔记。。。
触发器相关知识
数据库安全性相关知识