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