大哥救命~~

[baeeq0122]

--创建表

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表能更新总分信息，并且根据相应总分成绩给出等级，请高手帮帮忙~如果不改变以上代码大体结构最好，小第现在自学很迷茫~

[baeeq0122]

JL99000大哥，你写的触发器确实可读性，技术含量都很好~
但是这些我写的触发器都实现了~这些功能~就依照总分判定等级那里没弄好~嘿嘿~

[baeeq0122]

1楼的谢谢~呵呵~有我需要的~
不知道有没有大哥有现成的更标准的代码~供小弟参考~~

[qiuming0306]

你要的结果集是什么。，把你要的结果写出来！没太明白你的意思！
还有更新删除都要写触发器吧！

[baeeq0122]

大哥 简单点就是想在建立一个触发器，依实时变化的总分更新ZF表中的等级~
更复杂点目的就是想在每次输入成绩的时候，依据总分，和输入的课程次数判定等级，比如输入三门课程成绩后，总分>250为优秀，总分>210为良好，总分>150为及格，0<总分<150为不及格， 跪求各位大哥大嫂~大叔大妈~偶郁闷了N久，没人问~只能在BBS里发贴啦~~盼您的“馅饼”

[JL99000]

我帮你把触发器改了，就这里的问题：
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

接分了

[中国风]

--其它几个按以下方法改

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

[wzy_love_sly]

up