• 中南林业科技大学数据库实验七：存储过程和触发器

    文章目录

    一、目的与要求

    1. 掌握编写数据库存储过程的方法。
    2. 掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。
    3. 完成老师上课的案列（选）

    二、实验准备

    1. 了解编写存储过程和调用的T-SQL语法；
    2. 了解触发器的作用；
    3. 了解编写触发器的T-SQL语法。

    三、实验内容

    (一)存储过程

    1.储备知识

    1.1 创建存储过程

    有 output 则是输出参数，没有 output 则是输入参数

    create procedure | proc 存储过程名
     @参数名1 {参数数据类型}[=默认值] [output], 
     @参数名2 {参数数据类型}[=默认值] [output],
     ....
as
    SQL_statements

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    1.2 执行存储过程
    exec 存储过程名 参数值1 [output]，参数值2 [output]...

    • 1
    1.3 修改存储过程
    alter procedure 存储过程名
     参数名 {@参数数据类型}[=默认值] [output],
     参数名 {@参数数据类型}[=默认值] [output],
     ....
as
    SQL_statements


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    1.4 删除存储过程
    drop procedure 存储过程名

    • 1
    1.5 修改存储过程名
    [exec] sp_rename '原存储过程名','新存储过程名'

    • 1
    1.6 其它
    1. begin 和 end
    2. 声明变量：declare @变量名 数据类型(int,varchar,datetime,char)
    3. 自定义变量名不要和字段名一样

    📝 实例演示

    -- 创建存储过程
create procedure cal
	@a int,
	@b int,
	@sub int output,
	@sum int output
as
	set @sub = @a - @b
	set @sum = @a + @b
go

-- 调用存储过程
declare @sub int
declare @num int
exec cal 5,4,@sub output,@num output
select @sub,@sum


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2.创建存储过程实例

    在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

    create procedure getPractice
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) --mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	--1.查询指定院系的学生 --> D_ID
	--2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	--3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	--4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 --说明有院系存在
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    3.存储过程处理

    1️⃣ 分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。

    exec getPractice '法学院'

    • 1
    exec getPractice '材料科学与工程学院'

    • 1

    2️⃣ 利用系统存储过程sp_rename将getPractice更名为getPctStu

    [exec] sp_rename 'getPractice','getPctStu'

    • 1

    3️⃣ 修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并利用该存储过程以“法学院”为输入参数验证执行的结果

    alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			select count(*) -- 查询记录数(相同学号也没有关系，算作多条)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    验证：调用存储过程

    exec getPctStu '法学院'

exec getPctStu '法学院啊'

    • 1
    • 2
    • 3

    4️⃣ 再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。

    alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    验证：调用存储过程调用

    exec getPctStu '法学院'

exec getPctStu '法学院啊'

    • 1
    • 2
    • 3

    ⚠️注：“人数”和“人次数”是不同的，对某一学生而言，如果参与了多门实践课程，则“人次数”是指其参与的课程门数，而“人数”仍为1。

    (二)触发器

    1️⃣ 在studentdb数据库中建立一个具有审计功能的触发器：触发器名为tr_sc，功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中，sc_log表中有如下字段：操作类型type，学号st_id，课程号c_no，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，其中操作员设定默认值为user，操作时间默认值为系统时间。

    创建 sc_log 表：

    create table sc_log( 
    type char(6),
  	st_id char(10),
  	c_no char(10),
  	oldscore int,
  	newscore int,
  	uname varchar(20) default 'user',
  	udate datetime default getdate()
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    创建存储过程：

    -- 触发器生效的时候会有两个临时表 deleted inserted
-- 当删除或更新操作时候：deleted  对于删除，旧数据  对于更新，原来数据  --> 只有一条记录
-- 当执行插入或者更新操作：inserted  新记录 -- 只有一条记录

create trigger tr_sc
on s_c_info
for update,insert
as
-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作
	-- 如果是插入操作 --> deleted表为空
	-- 如果是更新操作 --> delete表有一条旧数据
	if (select count(*) from deleted) != 0
		-- 更新操作
		begin
			if update(score) -- 如果修改了score字段
			insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写
			-- deleted.score 对应 oldscore
			-- inserted score 对应 newscore
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  -- 表示这是插入操作
		begin
			-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为null
			insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字，所以为蓝色
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    测试：调用存储过程

    insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

-- 查看结果
select * from sc_log

    • 1
    • 2
    • 3
    • 4
    • 5
    update s_c_info
set score = 99
where st_id = '0603060108'
and c_no = '9720013'

-- 查看结果
select * from sc_log

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2️⃣ 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新，要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。

    create trigger tr_updasc
on s_c_info
for update
as
	if update(score)
	begin
		declare @oldscore int
		declare @newscore int
		set @oldscore = (select score from deleted)
		set @newscore = (select score from inserted)
		if @oldscore > @newscore
		begin
			print('新成绩不允许低于旧成绩，更新失败')
			rollback transaction
		end
	end

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    测试：调用存储过程

    -- 更新记录 --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

-- 更新记录不生效 --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    (三)查看存储

    用sp_helptext查看存储过程和触发器的代码

    sp_helptext '存储过程名'

    • 1

    🚩 补充：实验课考试题目全部代码（按顺序）

    create procedure getPractice
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) --mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	--1.查询指定院系的学生 --> D_ID
	--2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	--3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	--4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 --说明有院系存在
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

exec getPractice '法学院'
exec getPractice '法学院a'

sp_rename 'getPractice','getPctStu'

alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			select count(*) -- 查询记录数(相同学号也没有关系，算作多条)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

exec getPctStu '法学院'
exec getPctStu '法学院啊'

alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在') 
go

exec getPctStu '法学院'
exec getPctStu '法学院啊'

create table sc_log( 
    type char(6),
  	st_id char(10),
  	c_no char(10),
  	oldscore int,
  	newscore int,
  	uname varchar(20) default 'user',
  	udate datetime default getdate()
)

create trigger tr_sc
on s_c_info
for update,insert
as
-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作
	-- 如果是插入操作 --> deleted表为空
	-- 如果是更新操作 --> delete表有一条旧数据
	if (select count(*) from deleted) != 0
		-- 更新操作
		begin
			if update(score) -- 如果修改了score字段
			insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写
			-- deleted.score 对应 oldscore
			-- inserted score 对应 newscore
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  -- 表示这是插入操作
		begin
			-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为null
			insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字，所以为蓝色
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end


-- 插入操作
insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

-- 查看结果
select * from sc_log

-- 更新记录
update s_c_info
set score = 99
where st_id='0603060108'
and c_no='9720013'

-- 查看结果
select * from sc_log

-- 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新(update)
go -- 上一条语句结束
create trigger tr_updasc
on s_c_info
for update
as
	-- 要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。
	if update(score) -- 如果更新成绩才生效
		begin
			-- 分开写
			declare @oldscore int
			declare @newscore int -- 实际变量
			set @oldscore = (select score from deleted)
			set @newscore = (select score from inserted)
			if @newscore < @oldscore
				begin
					print('成绩不对')
					rollback transaction -- 回滚，所有操作均不生效
				end
		end
go -- 结束本次触发器执行


-- 更新记录 --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'

--验证更新成功
select * from s_c_info
where st_id='0603060108'
and c_no='9720013'

-- 更新记录不生效 --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'

--验证更新失败
select * from s_c_info
where st_id='0603060108'
and c_no='9720013'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177

    🚩 补充：腾讯会议讲解时写的SQL代码

    -- create procedure | proc 存储过程名
--      @参数名1 {参数数据类型}[=默认值] [output], 
--      @参数名2 {参数数据类型}[=默认值] [output],
--     ....
--as
--    SQL_statements

-- 计算两个数的和
create procedure cal
	@a int, -- 入参
	@b int, -- 入参
	@sum int output -- 出参
as
	set @sum = @a + @b
go

-- 定义变量
declare @sum1 int
-- 调用存储过程 execute：执行
-- 执行存储过程语法：exec 存储过程名 参数1，参数2 [output]
exec cal 5,4,@sum1 output -- 如果是出参一定要写output
select @sum1
go

-- 删除存储过程
-- drop table 表名
drop procedure cal
go

alter procedure cal  
	@a int, -- 入参
	@b int -- 入参
as
	select @a+@b
go

-- 修改存储过程名
-- 语法：[exec] sp_rename '旧存储过程名','新存储过程名'
sp_rename 'cal','newCal' -- 会有警告

exec sp_rename 'newCal','cal' -- 会有警告

-- begin end
alter procedure cal  
	@a int, -- 入参
	@b int -- 入参
as
begin
	select @a+@b
end

-- 在`studentdb数据库`中建立存储过程`getPractice`，查询指定院系(名称)(作为存储过程的输入参数)中
-- 参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

drop procedure getPractice
-- varchar 字符串类型
create procedure getPractice
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

-- 调用存储过程
exec getPractice '法学院'

exec getPractice '材料科学与工程学院' 
	
select count(*) from D_Info where D_Name='材料科学与工程学院'
select left('wkx cool',5)

-- 重命名：记得写单引号
sp_rename 'getPractice','getPctStu',

-- 存储过程名：getPctStu

-- 修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数
go
alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			select count(*) -- 查询记录数(相同学号也没有关系，算作多条)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

-- 并利用该存储过程以“法学院”为输入参数验证执行的结果
go
exec getPctStu '法学院'
go
--         再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。--> 一个学生只能出现一次
-- 原来题目：修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数 --> 可以有多次出现在查询出来的表中
alter procedure getPctStu
	-- 指定院系(名称)(作为存储过程的输入参数)
	@name varchar(24) -- mysql中表示24表示24个字符 sqlserver也不是utf-8
as
	-- 1.查询指定院系的学生 --> D_ID
	-- 2.查询该院系的学生有哪里(st_info) --》 查学生学号前两位为 D_ID的就可以了
	-- 3.“实践”课程 --> 先看s_c_info学生与课程对应关系
	-- 4.若院系不存在，返回提示信息。
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- 说明有院系存在
		begin
			-- distinct st_info.St_ID --> 过滤掉相同学号的，合并为一条记录
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- 查询到每位学生所选课程的课程号
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='实践'
		end
	else
		print('院系不存在')
go

-- sc_log表中，sc_log表中有如下字段：
-- 操作类型type，学号st_id，课程号c_no，旧成绩oldscore，新成绩newscore，操作员uname，操作时间udate，
-- 其中操作员设定默认值为user，操作时间默认值为系统时间。

create table sc_log(
	type char(10),
	st_id char(10),
	c_no char(10),
	oldscore int,
	newscore int,
	-- uname varchar(24) default user, --错的 --> 系统的关键字
	uname varchar(24) default 'user',
	udate datetime default getdate()    -- 在不同sql中函数名的差别巨大
)

-- 在studentdb数据库中建立一个具有审计功能的触发器：
-- 触发器名为tr_sc，
-- 功能要求：审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中

-- create trigger 触发器名
-- on 表名  -- 对那张表起作用
-- for [update,delete,insert]
-- as
-- SQL_statement

-- 触发器生效的时候会有两个临时表 deleted inserted
-- 当删除或更新操作时候：deleted  对于删除，旧数据  对于更新，原来数据  --> 只有一条记录
-- 当执行插入或者更新操作：inserted  新记录 -- 只有一条记录
  
go
create trigger tr_sc
on s_c_info
for update,insert
as
-- 审计在s_c_info表中对score字段的更新和插入操作，将这些操作记录到sc_log表中 --> 日志操作
	-- 如果是插入操作 --> deleted表为空
	-- 如果是更新操作 --> delete表有一条旧数据
	if (select count(*) from deleted) != 0
		-- 更新操作
		begin
			if update(score) -- 如果修改了score字段
			insert into sc_log(type,st_id,c_no,oldscore,newscore) -- 有默认值我们不用写
			-- deleted.score 对应 oldscore
			-- inserted score 对应 newscore
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  -- 表示这是插入操作
		begin
			-- 没有 oldscore ，那就忽略不写，则生成的记录中默认该值为null
			insert into sc_log(type,st_id,c_no,newscore) -- type是数据库的关键字，所以为蓝色
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end

-- 测试:插入操作
insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

select * from sc_log;

-- 更新记录
update s_c_info
set score = 99
where st_id='0603060108'
and c_no='9720013'

select * from sc_log;

-- 在s_c_info表上建立一个触发器tr_updasc，用于监控对成绩的更新(update)
go -- 上一条语句结束
create trigger tr_updasc
on s_c_info
for update
as
	-- 要求更新后的成绩不能比更新前低，如果新成绩低则取消操作，给出提示信息，否则允许更新。
	if update(score) -- 如果更新成绩才生效
		begin
			-- 分开写
			declare @oldscore int
			declare @newscore int -- 实际变量
			set @oldscore = (select score from deleted)
			set @newscore = (select score from inserted)
			if @newscore < @oldscore
				begin
					print('成绩不对')
					rollback transaction -- 回滚，所有操作均不生效
				end
		end
go -- 结束本次触发器执行

-- 更新记录 --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

-- 更新记录不生效 --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

go
sp_helptext tr_updasc -- 显示定义的SQL语句
go

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214
    • 215
    • 216
    • 217
    • 218
    • 219
    • 220
    • 221
    • 222
    • 223
    • 224
    • 225
    • 226
    • 227
    • 228
    • 229
    • 230
    • 231
    • 232
    • 233
    • 234
    • 235
    • 236
    • 237
    • 238
    • 239
    • 240
    • 241
    • 242
    • 243
    • 244
    • 245
    • 246
    • 247
    • 248
    • 249
    • 250
    • 251
    • 252
    • 253
    • 254
    • 255
    • 256
    • 257
    • 258
    • 259
    • 260
    • 261
    • 262
    • 263
  • 相关阅读:
    docker下redis备份文件dump.rdb获取
    devDependencies节点()
    Java学习笔记3.9.1 Lambda表达式 - Lambda表达式入门
    【李航统计学习】第 1 章 统计学习方法概论 笔记
    spring注解使用习惯-Control层前后端交互
    2021-07-09 springboot 整合shiro(前后端分离解决方案)
    photoshop2024免费插件Portraiture3
    关于DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC的一些发现
    OKR在项目管理中的应用：精准化目标管理
    挠场的科学丨五、二十一世纪的挠力文明
  • 原文地址：https://blog.csdn.net/qq_62982856/article/details/127399634