sql-server觸發器

一個勤勤懇懇的q大炮發表於2020-12-30
觸發器

1)	建立一個儲存過程,當考試伺服器選擇相應考試科目時,查詢出所有選修了該課程的學生。建立state表ExamState,顯示Sid,Cid,state,score,備註。state預設為00表示未登入,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,反之返回0create 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)

相關文章