导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

大哥救命~~

baeeq0122 2008-01-20 11:06:20
--创建表

if exists(select * from sysobjects where name='zf')
drop table zf

create table zf
(
学号 numeric(8,0) primary key,
姓名 varchar(20) not null,
总分 float null,
等级 char(4) null
)

if exists(select * from sysobjects where name='shuxue')
drop table shuxue

create table shuxue
(
学号 numeric(8,0),
分数 float null
)

if exists(select * from sysobjects where name='english')
drop table english

create table english
(
学号 numeric(8,0),
分数 float null
)

if exists(select * from sysobjects where name='computer')
drop table computer

create table computer
(
学号 numeric(8,0),
分数 float null
)

if exists(select * from sysobjects where name='yuwen')
drop table yuwen

create table yuwen
(
学号 numeric(8,0),
分数 float null
)

select * from zf
--------触发器--------------
if exists(select * from sysobjects where name='trig_shuxue')
drop trigger trig_shuxue

create trigger trig_shuxue on shuxue for insert as
declare @学号 int,@分数 int
select @学号=学号,@分数=分数 from shuxue
if @分数>=0 and @分数<=100
begin
update zf set 总分=总分+@分数 where @学号=学号
end
go

if exists(select * from sysobjects where name='trig_english')
drop trigger trig_english

create trigger trig_english on english for insert as
declare @学号 int,@分数 int
select @学号=学号,@分数=分数 from english
if @分数>=0 and @分数<=100
begin
update zf set 总分=总分+@分数 where @学号=学号
end
go

if exists(select * from sysobjects where name='trig_computer')
drop trigger trig_computer

create trigger trig_computer on computer for insert as
declare @学号 int,@分数 int
select @学号=学号,@分数=分数 from computer
if @分数>=0 and @分数<=100
begin
update zf set 总分=总分+@分数 where @学号=学号
end
go

if exists(select * from sysobjects where name='trig_yuwen')
drop trigger trig_yuwen

create trigger trig_yuwen on yuwen for insert as
declare @学号 int,@分数 int
select @学号=学号,@分数=分数 from yuwen
if @分数>=0 and @分数<=100
begin
update zf set 总分=总分+@分数 where @学号=学号
end
go

-----------------输入值----------------

insert into zf values(20050001,'tom',0,'?')
insert into zf values(20050002,'jane',0,'?')
insert into zf values(20050003,'bush',0,'?')
insert into zf values(20050004,'hani',0,'?')
insert into zf values(20050005,'toms',0,'?')
insert into zf values(20050006,'janes',0,'?')
--select * from zf
--drop table zf
insert into shuxue values(20050001,60)
insert into shuxue values(20050002,45)
insert into shuxue values(20050003,80)
insert into shuxue values(20050004,76)
insert into shuxue values(20050005,94)
insert into shuxue values(20050006,84)
--select * from shuxue
--select * from zf
--
insert into english values(20050001,54)
insert into english values(20050002,45)
insert into english values(20050003,82)
insert into english values(20050004,76)
insert into english values(20050005,81)
insert into english values(20050006,88)
--select * from english
--select * from zf
--
insert into computer values(20050001,42)
insert into computer values(20050002,64)
insert into computer values(20050003,80)
insert into computer values(20050004,76)
insert into computer values(20050005,86)
insert into computer values(20050006,84)
--select * from computer
--select * from zf
--
insert into yuwen values(20050001,86)
insert into yuwen values(20050002,84)
insert into yuwen values(20050003,87)
insert into yuwen values(20050004,78)
insert into yuwen values(20050005,42)
insert into yuwen values(20050006,41)
--select * from yuwen
select * from zf
--

各位大哥~~小弟今年18,刚学SQL不久,今天忙了一下午~打算玩个小“项目”,但是到现在,预期目的还没有达到~
目的如下:建立触发器,输入各科成绩后~ZS表能更新总分信息,并且根据相应总分成绩给出等级,请高手帮帮忙~如果不改变以上代码大体结构最好,小第现在自学很迷茫~
...全文
67 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
wzy_love_sly 2008-01-21
up
回复
中国风 2008-01-21
--其它几个按以下方法改

create trigger trig_shuxue on shuxue
for insert
as
begin
if (select count(1) from inserted)>1--防止同时新增多条
begin
raiserror 50005 N'防止同时插入多条'
rollback tran
return
end
declare @学号 int,@分数 int
select
@学号=学号,@分数=分数
from
shuxue
if @分数 between 0 and 100 --改一下写法
update zf
set
总分=总分+@分数,
等级=case when 总分+@分数 between 0 and 1000 then 1
when 总分+@分数 between 1001 and 2000 then 2 --
else 3 end
where
@学号=学号
end


回复
JL99000 2008-01-21
我帮你把触发器改了,就这里的问题:
if exists(select * from sysobjects where name='trig_shuxue')
drop trigger trig_shuxue
go
create trigger trig_shuxue
on shuxue for insert
as
declare @学号 int,@分数 int
declare cur cursor for select 学号,分数 from inserted
open cur
fetch next from cur into @学号,@分数
while(@@fetch_status=0)
begin
if (@分数>=0 and @分数 <=100)
begin
update zf set 总分=总分+@分数 where @学号=学号
end
fetch next from cur into @学号,@分数
end
close cur
deallocate cur
go

if exists(select * from sysobjects where name='trig_english')
drop trigger trig_english
go
create trigger trig_english
on english for insert
as
declare @学号 int,@分数 int
declare cur cursor for select 学号,分数 from inserted
open cur
fetch next from cur into @学号,@分数
while(@@fetch_status=0)
begin
if (@分数>=0 and @分数 <=100)
begin
update zf set 总分=总分+@分数 where @学号=学号
end
fetch next from cur into @学号,@分数
end
close cur
deallocate cur
go
if exists(select * from sysobjects where name='trig_computer')
drop trigger trig_computer
go
create trigger trig_computer
on computer for insert
as
declare @学号 int,@分数 int
declare cur cursor for select 学号,分数 from inserted
open cur
fetch next from cur into @学号,@分数
while(@@fetch_status=0)
begin
if (@分数>=0 and @分数 <=100)
begin
update zf set 总分=总分+@分数 where @学号=学号
end
fetch next from cur into @学号,@分数
end
close cur
deallocate cur
go
if exists(select * from sysobjects where name='trig_yuwen')
drop trigger trig_yuwen
go
create trigger trig_yuwen
on yuwen for insert
as
declare @学号 int,@分数 int
declare cur cursor for select 学号,分数 from inserted
open cur
fetch next from cur into @学号,@分数
while(@@fetch_status=0)
begin
if (@分数>=0 and @分数 <=100)
begin
update zf set 总分=总分+@分数 where @学号=学号
end
fetch next from cur into @学号,@分数
end
close cur
deallocate cur
go

接分了
回复
baeeq0122 2008-01-21
大哥 简单点就是想在建立一个触发器,依实时变化的总分更新ZF表中的等级~
更复杂点目的就是想在每次输入成绩的时候,依据总分,和输入的课程次数判定等级,比如输入三门课程成绩后,总分>250为优秀,总分>210为良好,总分>150为及格,0<总分<150为不及格, 跪求各位大哥大嫂~大叔大妈~偶郁闷了N久,没人问~只能在BBS里发贴啦~~盼您的“馅饼”
回复
qiuming0306 2008-01-21
你要的结果集是什么。,把你要的结果写出来!没太明白你的意思!
还有更新删除都要写触发器吧!
回复
baeeq0122 2008-01-21
1楼的谢谢~呵呵~有我需要的~
不知道有没有大哥有现成的更标准的代码~供小弟参考~~
回复
baeeq0122 2008-01-21
JL99000大哥,你写的触发器确实可读性,技术含量都很好~
但是这些我写的触发器都实现了~这些功能~就依照总分判定等级那里没弄好~嘿嘿~
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告