数据库上机实验五、六（触发器、数据库安全性、数据备份与还原）

本文链接：https://blog.csdn.net/QAZJOU/article/details/124602241

实验教材李春葆《数据库原理及应用》
实验内容五题目
1）完成P325第7题至第10题。
2）完成P396上机实验题8各题目。
3）完成P396上机实验题9各题目。

实验内容六
1）实现test、library数据库的备份与还原。
2）实现test、library数据库的分离与附加。
3）完成上机实验题10。

前言1：实验六会出现一堆错误，建议自行百度，或者留言评论，当然了我这里也给出一些解决办法，

附在了注释里面请先详细看注释内容，需要的可以查看。

前言2：本次实验五六直接写在一起了，后续将对数据库疑问进行答疑。

前言3：SQL 触发器是一个数据库对象，它在数据库中发生事件时触发。当数据库表发生更改（例如插入、更新或删除记录）时，我们可以执行一个 SQL 查询，该查询将在数据库中“做某事”。例如，可以在数据库表中的记录插入上设置触发器。
DDL 触发器被触发以响应以 Create、Alter 和 Drop 开头的 DDL（数据定义语言）命令事件，例如 Create_table、Create_view、drop_table、Drop_view 和 Alter_table。
DML 触发器被触发以响应以插入、更新和删除开头的 DML（数据操作语言）命令事件。像 insert_table、Update_view 和 Delete_table。

实验五

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

use school2128
go
create trigger  triscoreinsert on score2128 after insert
as
    begin
        declare @name nvarchar(10)
        select @name=inserted.课程号 from inserted
        if not exists(select 课程号 from course2128 where 课程号=@name)
        begin
            raiserror('课程号不正确,重新输入',16,1)
            rollback
        end
    end
go --测试数据
insert into score2128 values ('128','3-105','98')

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

create trigger triscoreupdate on score2128 after  update
as
    begin
        declare @cname nvarchar(10)
        select @cname=inserted.课程号 from inserted
        if not exists(select 课程号 from score2128 where  课程号=@cname)
        begin
            raiserror ('修改的课程号有误',16,1)
        end
    end
go --测试数据,不能修改的数据
update score2128 set 课程号='4-102' where 课程号='4-101'
go

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

-- P325-9
CREATE TRIGGER trig3
	ON score2128 AFTER UPDATE
AS
begin
	DECLARE @grade FLOAT
	SELECT @grade=inserted.分数 FROM inserted
	print @grade
	IF (@grade <1 OR  @grade >100)
	BEGIN
	RAISERROR('Error',16,1)
	ROLLBACK
	end
end
GO


UPDATE score2128
SET 分数=97
WHERE 学号='101' AND 课程号='3-105'

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

-- P325-10
use student2128
go
create  trigger  triteach on teacher2128 after delete
as
    begin
        raiserror ('不能删除记录',16,1)
        rollback
    end
go --测试数据，不会成功
delete  teacher2128 where 编号=804
go

5.在borrow表中建一个触发器，完成：如果借阅的图书是”网络工程“，那么把该书记录保存在borrow1表中。

--Test 8-1
use Library2128
create  table borrow1(
    学号 nvarchar(10),
    图书编号 nvarchar(10) primary key (图书编号,学号),
    借书日期 datetime
)
go
create  trigger  tribor on borrow2128 for insert,update
as
    insert  borrow1  --插入数据到borrow1表中
    select  ins.*
    from inserted ins,book2128 b
    where ins.图书编号=b.图书编号 and b.图书名='网络工程'
go --测试数据，此时请在borrow1表中查看记录
insert into borrow2128 values ('19','20090','2022-4-27')
go --查看数据
select * from borrow1

6.在borrow表建一个触发器，完成：当删除borrow表中任何记录时，将记录保存在borrow1中

-- Test 8-2
create  trigger  tribordrop on borrow2128 for delete
as
    insert  borrow1  --插入数据到borrow1表中
    select *
    from deleted
go --测试数据，此时请在borrow1表中查看记录
delete  borrow2128 where 学号 =4
go
select * from borrow1

7.删除前面的触发器

-- Test 8-3
drop trigger tribor
drop trigger tribordrop

8.创建一个登录账号 Liblog，其密码为“123456”。

--Test 9-1
create  login  Liblog with password ='123456'

9.为 Liblog 登录账号在 Library 数据库中创建一个数据库用户账号 Liblog。

--Test 9-2
use  Library2128
create user Libuer for login  Liblog

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

-- Test 9-3
use  Library2128
grant create table  to Libuer
go
revoke create table from Libuer

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

-- Test 9-4
grant insert ,update ,delete  on student2128 to Libuer
go
revoke insert ,update ,delete from Libuer

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

-- Test 9-5
drop login Liblog
go
drop user Libuer

实验六
序号从1开始了,请自己运行的时候删除注释！！！

1.实现test、library数据库的备份与还原。

-- 实现test  Libra备份与还原,按照我的方法来这样就可以看到在文件里面的备份文件了
-- 请在D盘建立DB\Backup 文件夹和建立DB\Back
-- 先进行Library的备份
use Library2128
go
exec sp_addumpdevice @devtype ='disk',@logicalname='Backup1',@physicalname='D:\DB\Backup\test.bak'
go -- 显示路径
select * from sysdevices
go -- 拷贝过去，之前已经完成了但是必须要进行权限操作,这样子才会看见文件
backup database Library2128 to Backup1
-- go 删除备份
--exec sp_dropdevice 'Backup1'
--下面进行test的备份
use test
exec sp_addumpdevice @devtype ='disk',@logicalname='Backup2',@physicalname='D:\DB\Back\test2.bak'
go
backup database test to Backup2
--go
exec sp_dropdevice 'Backup2'

-- 下面对数据库进行还原
-- 还原数据库 如果你执行这段话没有报错就可以，但是如果出现“无法删除因为library也在”，那么请不要先执行这段话！！！可以先进行我下面数据库的分离操作，然后再还原，本人亲测有效。
use master
go
restore  database Library2128 from Backup1 with replace
restore  database test from Backup2 with replace
go

2.实现test、library数据库的分离与附加

分离数据库会没有，那么请先执行上述的还原操作，当然了如果你能找到ldf,mdf文件的话就可以重新追加，不过还是介意使用还原操作比较方便！
首先说明：
.ldf
LDF是SQL的日志文件，是不能直接打开的。可以在sql server中附加数据库，查看里面的内容。
.mdf
MDF是SQL Server数据库文件。
但是本次实验是没有另外的ldf和mdf文件故会报错，但是如果愿意可以自己重新制作一个mdf和ldf文件进行导入。

-- 实现test、library数据库的分离

exec sp_detach_db  Library2128
go
--如果出现无法分离数据库，因为该数据库正在使用，请输入注释的命令，然后再detach，database就是你的数据库名字
-- ALTER DATABASE  test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
exec sp_detach_db  test
go

-- 数据库附加 注意此时使用windows验证登录数据库(不要使用sa登录），同时要包含有mdf、ldf文件，但是一般是没有的（除非你自己创建一个），故会显示错误，但是本语句没有问题
exec sp_attach_db @dbname='test',@filename1='D:\DB\sql\test.mdf',@filename2='D:\DB\sql\test.ldf'
go
exec sp_attach_db @dbname = 'Library2128',@filename1='D:\DB\sql\test1.mdf',@filename2='D:\DB\sql\test1.ldf'
go

3.创建一个数据库备份设备backdisk,对应的磁盘文件是"D:\DB\Backup\backup.bak"

--Test 10-1 无语了前面不就是让我们做了吗又在实验里做,简直没话说
use Library2128
go
exec  sp_addumpdevice @devtype = 'disk',@logicalname='backdisk',@physicalname='D:\DB\Backup\test4.bak'
go

3.备份到backdisk文件中

-- Test 10-2 此时看自己的文件夹
backup database Library2128 to backdisk

4.从backdisk恢复Library数据库

-- Test 10-3 因为前面进行过了所以不会有问题
use master
restore database  Library2128 from backdisk with  replace

5.删除备份

-- Test 10-4
exec  sp_dropdevice @logicalname = 'backdisk'