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)

Oracle觸發器觸發級別
2011-11-30
Oracle觸發器
Oracle觸發器6(建立系統事件觸發器)
2017-01-15
Oracle觸發器事件
根據業務寫觸發器（oracle觸發器片）
2016-05-23
觸發器Oracle
MySQL觸發器
2021-05-11
MySql觸發器
mysql——觸發器
2017-03-18
MySql觸發器
mysql 觸發器
2014-07-30
MySql觸發器
SQL觸發器
2010-06-25
SQL觸發器
Mysql觸發器：
2005-06-22
MySql觸發器
Oracle觸發器
2007-04-24
Oracle觸發器
【SQL Server】-- 一觸即發之觸發器
2015-02-03
SQLServer觸發器
mysql繞過行觸發器，實現語句觸發器
2015-06-17
MySql觸發器
sqlserver 列觸發器
2020-04-04
SQLServer觸發器
除錯觸發器
2019-06-05
除錯觸發器
建立MySQL觸發器
2021-06-04
MySql觸發器
SqlServer-觸發器
2018-11-22
SQLServer觸發器
MySQL使用觸發器
2019-01-23
MySql觸發器
MySql-觸發器
2020-09-29
MySql觸發器
MySQL 建立觸發器
2016-07-03
MySql觸發器
mysql建立觸發器
2014-06-30
MySql觸發器
SQL Server 觸發器
2013-08-01
SQLServer觸發器
postgresql 觸發器操作
2013-06-16
SQL觸發器
觸發器詳解
2015-06-05
觸發器
建立SQL觸發器
2010-09-15
SQL觸發器
ORACLE DDL觸發器
2012-07-09
Oracle觸發器
SQL Server觸發器
2006-09-25
SQLServer觸發器
淺談觸發器
2016-03-02
觸發器
Oracle之觸發器
2012-08-02
Oracle觸發器
SQL觸發器（二）
2009-07-23
SQL觸發器
oracle ddl 觸發器
2009-11-26
Oracle觸發器
瞭解SQL Server觸發器及觸發器中的事務AW
2022-03-21
SQLServer觸發器
SQL Sever 2000中的前觸發器和後觸發器
2009-03-16
SQL觸發器
MySQL觸發器介紹
2021-08-02
MySql觸發器
pgsql編寫觸發器
2017-02-21
SQL觸發器
ORACLE觸發器詳解
2014-03-06
Oracle觸發器
Oracle登陸觸發器
2015-12-23
Oracle觸發器
DML操作 DDL觸發器
2014-06-25
觸發器
oracle 觸發器-表同步
2014-10-20
Oracle觸發器
Sql Server系列：觸發器
2014-11-27
SQLServer觸發器

sql-server觸發器

相關文章