如题:触发器为了实现每位学生的加权平均分的计算:之前已近建了表:
SC表:
CREATE TABLE SC
(
SNO CHAR(6),
CNO CHAR(4),
Grade float,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES Student(SNO),
FOREIGN KEY(CNO)REFERENCES Course(CNO)
)
Course 表:
CREATE TABLE Course
(
CNO CHAR(4)primary key,
CNAME CHAR(20),
Ccredit int
)create table add_1
(
SNO CHAR(10),--学号
avgrage float --加权平均分
)
下面是向SC输入数据(所触发的实现求学生加权平均分)的触发器:after insert ,update,delete
as
begin
declare @average float;
declare @sno char(10);
declare @credit int;
declare @grade float;
declare @num int;
declare @total_grade float;
declare @total_credit int;
declare cursor_1 cursor for select sno,grade,Ccredit from course, sc where sc.sno=@sno and sc.cno=course.cno;
open cursor_1
fetch next from cursor_1 into @sno,@grade,@credit;
while(@@fetch_status=0)
begin
set @total_grade=@total_grade+@grade*@total_credit;
set @total_credit=@total_credit+@credit;
end
set @average = @total_grade/@total_credit;
select @num=count(*)from add_1 where add_1.SNO=@sno;
if @num>0
update add_1 set avgrage=@average where add_1.SNO=@sno;
else if @num=0 and @sno is not null
insert into add_1 values(@sno,@average);
close cursor_1;
end;向SC表插入数据:insert into sc values('1001','1','90')
insert into sc values('1001','2','75')
insert into sc values('1001','3','64')
insert into sc values('1001','4','95')
insert into sc values('1002','1','86')
insert into sc values('1002','2','81')
insert into sc values('1002','3','72')
insert into sc values('1002','4','97')
insert into sc values('1003','1','60')
insert into sc values('1003','2','63')
insert into sc values('1003','3','84')
insert into sc values('1003','4','96')
运行了很多次add_1表一直没有数据,求各位帮帮忙,这个触发器错哪里了,分少了点不过全部奉上了·谢谢·
SC表:
CREATE TABLE SC
(
SNO CHAR(6),
CNO CHAR(4),
Grade float,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES Student(SNO),
FOREIGN KEY(CNO)REFERENCES Course(CNO)
)
Course 表:
CREATE TABLE Course
(
CNO CHAR(4)primary key,
CNAME CHAR(20),
Ccredit int
)create table add_1
(
SNO CHAR(10),--学号
avgrage float --加权平均分
)
下面是向SC输入数据(所触发的实现求学生加权平均分)的触发器:after insert ,update,delete
as
begin
declare @average float;
declare @sno char(10);
declare @credit int;
declare @grade float;
declare @num int;
declare @total_grade float;
declare @total_credit int;
declare cursor_1 cursor for select sno,grade,Ccredit from course, sc where sc.sno=@sno and sc.cno=course.cno;
open cursor_1
fetch next from cursor_1 into @sno,@grade,@credit;
while(@@fetch_status=0)
begin
set @total_grade=@total_grade+@grade*@total_credit;
set @total_credit=@total_credit+@credit;
end
set @average = @total_grade/@total_credit;
select @num=count(*)from add_1 where add_1.SNO=@sno;
if @num>0
update add_1 set avgrage=@average where add_1.SNO=@sno;
else if @num=0 and @sno is not null
insert into add_1 values(@sno,@average);
close cursor_1;
end;向SC表插入数据:insert into sc values('1001','1','90')
insert into sc values('1001','2','75')
insert into sc values('1001','3','64')
insert into sc values('1001','4','95')
insert into sc values('1002','1','86')
insert into sc values('1002','2','81')
insert into sc values('1002','3','72')
insert into sc values('1002','4','97')
insert into sc values('1003','1','60')
insert into sc values('1003','2','63')
insert into sc values('1003','3','84')
insert into sc values('1003','4','96')
运行了很多次add_1表一直没有数据,求各位帮帮忙,这个触发器错哪里了,分少了点不过全部奉上了·谢谢·
CREATE TABLE Student
(
SNO CHAR (6) PRIMARY KEY,
SNAME CHAR(8),
SSEX CHAR(4),
SAGE INT,
SDEPT CHAR (4)
)
create trigger insert_rank on sc
要求是自动统计所有学生的加权平均分。
这句话就错了,按你这样写@sno岂不是你要输入的参数?触发器不能带有参数可以自己定义参数。
after insert ,update,delete
as
begin
declare @average float;
declare @sno char(10);
declare @credit int;
declare @grade float;
declare @num int;
declare @total_grade float;
declare @total_credit int;
declare cursor_1 cursor for select sno,grade,Ccredit from course, sc where sc.cno=course.cno;
open cursor_1
fetch next from cursor_1 into @sno,@grade,@credit;
while(@@fetch_status=0)
begin
set @total_grade=@total_grade+@grade*@total_credit;
set @total_credit=@total_credit+@credit;
end
set @average = @total_grade/@total_credit;
select @num=count(*)from add_1 where add_1.SNO=@sno;
if @num>0
update add_1 set avgrage=@average where add_1.SNO=@sno;
else if @num=0 and @sno is not null
insert into add_1 values(@sno,@average);
close cursor_1;
end;
add_1还是空的表··