sql-server觸發器

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

觸發器

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)

mysql觸發器
2024-05-14
MySql觸發器
D觸發器
2024-11-30
觸發器
SqlServer-觸發器
2018-11-22
SQLServer觸發器
logon觸發器for dba
2019-04-16
Go觸發器
MySQL使用觸發器
2019-01-23
MySql觸發器
語句觸發器
2018-06-14
觸發器
MySql-觸發器
2020-09-29
MySql觸發器
sqlserver 列觸發器
2020-04-04
SQLServer觸發器
除錯觸發器
2019-06-05
除錯觸發器
建立MySQL觸發器
2021-06-04
MySql觸發器
瞭解SQL Server觸發器及觸發器中的事務AW
2022-03-21
SQLServer觸發器
觸發器 REFERENCING OLD AS OLD
2018-10-20
觸發器
行為和觸發器
2024-04-05
觸發器
MySQL觸發器介紹
2021-08-02
MySql觸發器
Oracle開發基礎-觸發器
2020-06-11
Oracle觸發器
SQL Server：觸發器詳解
2018-06-08
SQLServer觸發器
mvvm模式事件觸發器[wpf]
2024-03-18
MVVM模式事件觸發器
MySQL入門-- TRIGGER（觸發器）
2019-05-31
MySql觸發器
SQL觸發器例項講解
2018-09-10
SQL觸發器
禁止oracle表的觸發器trigger
2020-10-14
Oracle觸發器
MSMQ 觸發器安裝失敗
2019-05-10
MQ觸發器
SQL Server 觸發器詳情HOPP
2022-03-01
SQLServer觸發器
MySQL觸發器的使用規則
2021-06-14
MySql觸發器
SqlServer觸發器的建立與使用
2021-04-01
SQLServer觸發器
暫存器，觸發器，三極體小結
2024-06-27
觸發器
SQL-Server筆記
2018-03-20
SQLServer筆記
sql-server檢視
2020-12-29
SQLServer
雲開發 CloudBase 已上線觸發器能力
2021-10-19
Cloud觸發器
OGG DDL觸發器引發的故障系列（一）
2023-03-30
觸發器
淺入淺出SQL Server 觸發器
2018-11-11
SQLServer觸發器
oracle儲存過程和觸發器
2019-01-06
Oracle儲存過程觸發器
數位電路中的觸發器
2020-10-13
觸發器
[MySQL光速入門]026 觸發器 trigger!!!
2019-04-18
MySql觸發器
25. 使用MySQL之使用觸發器
2024-11-12
MySql觸發器
資料庫檢視，索引，觸發器
2024-08-16
資料庫索引觸發器
資料庫的觸發器的使用
2024-07-14
資料庫觸發器
MySQL全面瓦解17：觸發器相關
2021-01-19
MySql觸發器
WPF基礎—佈局，樣式，觸發器
2024-10-04
觸發器

sql-server觸發器

相關文章