觸發器
1） 建立一個儲存過程，當考試伺服器選擇相應考試科目時，查詢出所有選修了該課程的學生。建立state表ExamState，顯示Sid，Cid，state，score，備註。state預設為0（0表示未登入，1表示已登入，2表示正在考試，3表示交卷，4表示異常）。備註狀態預設為Null。建立S-ST表（Sid，題號，答案，標準答案）。
create proc examC @cname nvarchar(20)
as
begin
if exists(select * from sysobjects where name='examstate' and type='U')
drop table examstate
create table examstate(Sid char(10),sname nvarchar(20),Cid nchar(3),state int default 0,score int default Null,beizhu nvarchar(20) default Null)
insert into examstate
select S.Sid,sname,C.Cid,0,NULL,Null from SC,C,S where SC.Cid=C.Cid and S.Sid=SC.Sid and cname=@cname
if exists(select * from sysobjects where name='S_ST' and type='U')
drop table S_ST
create table S_ST(Sid nchar(10),Tid int,answer nchar(1),biaozhunanser nchar(1))
end
exec examC '資料庫原理'
select * from examstate
select * from S_ST
2) 建立一個儲存過程，如果examstate存在該Sid，將examstate狀態置為1，返回1，表示登入成功。反之返回0，表示登入失敗。
create proc loginproc @sid varchar(10),@result varchar(50) output
as
begin
if exists(select * from examstate where Sid=@sid)
begin
declare @zt int
select @zt=state from examstate where Sid=@sid
if(@zt=3)
set @result='已經交卷，不能重複登入'
else if(@zt=4)
set @result='請監考老師輸入二次登陸密碼'
else if(@zt=0)
begin
set @result='登陸成功'
update examstate set state=1 where Sid=@sid
end
end
else
set @result='不存在該使用者'
end
declare @res varchar(50)
exec loginproc '2017071102',@res output
print @res
select * from examstate
3） 建立一個觸發器，當ExamState表中state從0變成1時，隨機從ST表中抽取10到試題存入S—_ST表中。
create trigger ct on examstate after update
as
begin
declare @statestart int,@stateend int,@sid char(10)
select @statestart=state,@sid=Sid from deleted
select @stateend=state from inserted
if(@statestart=0 and @stateend=1)
begin
insert into S_ST (Tid,answer ,biaozhunanser,Sid)
SELECT top 10 id,NULL,answer,@sid FROM ST ORDER BY NEWID()
end
end
select * from S_ST
select * from ST
delete from S_ST
update examstate set state=1 where Sid='2017071102'
create proc showexamdata @pageIndex int,@pageSize int,@sid nchar(10)
as
begin
declare @start int,@end int
set @start = (@pageIndex - 1) * @pageSize + 1
set @end = @pageIndex * @pageSize
select problem,optionA,optionB,optionC,optionD from ST where id in(
select Tid from
(
select *,rank() OVER (ORDER BY Tid) AS RowNo
from S_ST
where sid=@sid
)
as list
where RowNo between @start and @end
)
end
exec showexamdata 2,5,'2017071102'
4） 建立一個觸發器，當ExamState表中state變成3時，計算該考生的分數（滿分100）寫入ExamState表中。
create trigger getscore on examstate after update
as
begin
declare @state int,@sid char(10)
select @state=state,@sid=Sid from inserted
if(@state=3)
begin
declare @score int
select @score=count(*)*10 from S_ST where Sid=@sid and S_ST.biaozhunanser=answer
update examstate set score=@score where Sid=@sid
end
end
update examstate set state=3 where Sid='2017071102'
5） 建立一個儲存過程，當伺服器收分時，如果還沒有交卷的考返回0,如果全部交卷返回1,並將state為0的學生在備註欄改為缺考;將state為3的備註為正常。
CREATE proc endexam @result int output
as
begin
if exists(select * from examstate where state in(1,2,4))
set @result=0
else
begin
set @result=1
update examstate set score=0 , beizhu='缺考' where state=0
update examstate set beizhu='正常' where state=3
end
end
declare @res int
exec endexam @res output
print @res
select * from examstate
6)建立儲存過程,將ExamState表備份至備份表ExamBackup中，如果備份成功返回1，反之返回0。
create proc backexamstate @result int output
as
begin
select * into ExamBackup from examstate
set @result=1
end
declare @res int
exec backexamstate @res output
print @res
select * from exambackup
7)建立觸發器，當刪除examstate表時，檢查系統是否存在備份表ExamBackup，如果不存在則不允許刪除。
create trigger checkbf on database for drop_table
as
begin
if not exists(select * from sysobjects where name='ExamBackup' and type='U')
rollback;
if not exists(select * from sysobjects where name='ExamSTBackup' and type='U')
select * into ExamSTBackup from S_ST;
drop table S_ST
end
drop table examstate
資料表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar(5) primary key,
name nchar(10) not null,
sex nchar(2) default '男',
workingtime datetime,
plandscape nchar(20),
degree nchar(10),
title nvarchar(6),
department nvarchar(20),
tel nchar(11)
)
create table S(
Sid nchar(10) primary key,
sname nchar(10),
sex nchar(2) default '男',
birthday Date,
age as datediff(yy,birthday,getdate()),
entrydate date,
plandscape nchar(10) default '團員',
nativeplace nvarchar(20),
class as substring(Sid,7,2)
)
create table C(
Cid nchar(3) primary key,
cname nvarchar(20) Not null,
category nchar(4) Not null check(category in('考試','考查')) default '考試',
credit int,
Tid nchar(5) foreign key references T(Tid) on update cascade
)
create table SC(
Sid nchar(10) foreign key references S(Sid) on update cascade on delete cascade,
Cid nchar(3),
score int check(score between 0 and 100),
primary key(Sid,Cid),
foreign key(Cid) references C(Cid) on update cascade on delete cascade
)
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95011', N'趙西明 ', N'男 ', CAST(0x0000768300000000 AS DateTime), N'群眾 ', N'碩士 ', N'副教授', N'軟體', N'13733152369')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95012', N'李小平 ', N'男 ', CAST(0x0000775E00000000 AS DateTime), N'黨員 ', N'碩士 ', N'教授', N'計算機系', N'13733152370')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95013', N'張爽 ', N'男 ', CAST(0x00007BC700000000 AS DateTime), N'群眾 ', N'本科 ', N'副教授', N'計算機系', N'13733152371')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95014', N'李麗寧 ', N'女 ', CAST(0x00007BC700000000 AS DateTime), N'黨員 ', N'碩士 ', N'副教授', N'計算機系', N'13733152372')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95015', N'張金明 ', N'男 ', CAST(0x0000812C00000000 AS DateTime), N'群眾 ', N'碩士 ', N'講師', N'計算機系', N'13733152373')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95016', N'李燕 ', N'女 ', CAST(0x000082C800000000 AS DateTime), N'黨員 ', N'碩士 ', N'講師', N'計算機系', N'13733152374')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95017', N'宛平 ', N'女 ', CAST(0x0000840B00000000 AS DateTime), N'群眾 ', N'博士 ', N'副教授', N'軟體', N'13733152375')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95018', N'陳江川 ', N'男 ', CAST(0x0000883900000000 AS DateTime), N'群眾 ', N'博士 ', N'講師', N'軟體', N'13733152376')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95019', N'郭新 ', N'男 ', CAST(0x00008CAA00000000 AS DateTime), N'黨員 ', N'博士 ', N'講師', N'軟體', N'13733152377')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071101', N'張佳 ', N'女 ', CAST(0x3F1D0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071102', N'好生 ', N'男 ', CAST(0xCD1E0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'廣州順德')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071203', N'徐克 ', N'男 ', CAST(0xBF210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071204', N'葉飛 ', N'女 ', CAST(0xD1210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'黨員 ', N'上海')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071205', N'任偉 ', N'男 ', CAST(0x7B220B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'北京順義')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071206', N'江賀 ', N'男 ', CAST(0x02200B00 AS Date), CAST(0x3D3D0B00 AS Date), N'黨員 ', N'福建廈門')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'101', N'計算機基礎', N'考試 ', 2, N'95011')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'102', N'C語言', N'考試 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'103', N'計算機組成原理', N'考試 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'104', N'組合語言', N'考試 ', 3, N'95014')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'105', N'Java語言', N'考查 ', 2, N'95015')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'106', N'作業系統', N'考試 ', 3, N'95016')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'107', N'資料結構', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'108', N'編譯原理', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'109', N'網路基礎', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'110', N'資料庫原理', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'120', N'SQLServer', N'考查 ', 2, N'95018')
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'101', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'102', 70)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'103', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'105', 80)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'102', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'106', 68)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'107', 85)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'109', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'110', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'120', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'102', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'104', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'105', 79)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'107', 55)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'110', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'120', 67)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'101', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'102', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'105', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'106', 95)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'108', 91)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'110', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'101', 63)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'102', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'103', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'107', 58)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'108', 74)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'109', 74)