/*==============================================================*/
/* Table: wzY_syllabus 课程信息表 */
/*==============================================================*/
create table wzY_syllabus (
Id int identity, --标识Id
Number varchar(20) not null, --课程编号
Name varchar(28) null, --课程名称
Period int null, --周学时
Kind varchar(16) null, --课程性质,考试/考查
Type varchar(16) null, --课程类型,公共基础/专业基础/专业课/公共选修/专业选修
CreditHour smallint null, --学分
constraint PK_WZY_SYLLABUS primary key (Number)
)/*==============================================================*/
/* Table: wzY_Grade 成绩表 */
/*==============================================================*/
create table wzY_Grade (
Id int identity, --标识Id
StuNumber varchar(20) not null, --外键,学号
SylNumber varchar(20) not null, --外键,课程编号
Mark int null, --分数
constraint PK_WZY_GRADE primary key (StuNumber, SylNumber) --联合主键(学号,课程编号)
)
/*==============================================================*/
/* Table: wzY_CreditHour 总学分分表 */
/*==============================================================*/
create table wzY_CreditHour (
Id int identity, --标识Id
StuNumber varchar(20) not null, --外键,主键,学号
CreditHour int null default 0, --总学分
constraint PK_WZY_CREDITHOUR primary key (StuNumber)
)我要实现:当插入(成绩表)数据时,寻找到课程的编号(课程表),在课程编号得到这门课程的学分,然后累加到对应学号的(学分表)总学分中...
涉及到3张表,可以实现吗?
/* Table: wzY_syllabus 课程信息表 */
/*==============================================================*/
create table wzY_syllabus (
Id int identity, --标识Id
Number varchar(20) not null, --课程编号
Name varchar(28) null, --课程名称
Period int null, --周学时
Kind varchar(16) null, --课程性质,考试/考查
Type varchar(16) null, --课程类型,公共基础/专业基础/专业课/公共选修/专业选修
CreditHour smallint null, --学分
constraint PK_WZY_SYLLABUS primary key (Number)
)/*==============================================================*/
/* Table: wzY_Grade 成绩表 */
/*==============================================================*/
create table wzY_Grade (
Id int identity, --标识Id
StuNumber varchar(20) not null, --外键,学号
SylNumber varchar(20) not null, --外键,课程编号
Mark int null, --分数
constraint PK_WZY_GRADE primary key (StuNumber, SylNumber) --联合主键(学号,课程编号)
)
/*==============================================================*/
/* Table: wzY_CreditHour 总学分分表 */
/*==============================================================*/
create table wzY_CreditHour (
Id int identity, --标识Id
StuNumber varchar(20) not null, --外键,主键,学号
CreditHour int null default 0, --总学分
constraint PK_WZY_CREDITHOUR primary key (StuNumber)
)我要实现:当插入(成绩表)数据时,寻找到课程的编号(课程表),在课程编号得到这门课程的学分,然后累加到对应学号的(学分表)总学分中...
涉及到3张表,可以实现吗?
on wzY_Grade
for insert
as
begin
update a
set CreditHour = isnull(CreditHour) + b.CreditHour
from inserted i
left join wzY_CreditHour a on i.StuNumber= a.StuNumber
join wzY_syllabus c on i.SylNumber = c.Number
end if
go
on wzY_Grade
for insert
as
begin
update wzY_CreditHour
set CreditHour = isnull(CreditHour) + case when i.>60 then b.CreditHour else 0 end
from inserted i
left join wzY_CreditHour a on i.StuNumber= a.StuNumber
join wzY_syllabus c on i.SylNumber = c.Number
end if
go--修改石头哥的
if object_id('test_tri') is not null
drop trigger test_tri
go
create trigger test_tri on wzY_Grade
for insert
as
if exists(Select Id from wzY_CreditHour where StuNumber in (Select StuNumber from inserted))
update wzY_CreditHour set wzY_CreditHour.CreditHour=IsNull(wzY_CreditHour.CreditHour,0) + wzY_syllabus.CreditHour
from inserted I inner join wzY_syllabus on I.SylNumber=wzY_syllabus.Number
else begin
insert into wzY_CreditHour(StuNumber,CreditHour)
select I.StuNumber,wzY_syllabus.CreditHour
from inserted I inner join wzY_syllabus on I.SylNumber=wzY_syllabus.Number
end
go
if object_id('test_tri') is not null
drop trigger test_tri
go
create trigger test_tri on wzY_Grade
for insert
as
if exists(Select Id from wzY_CreditHour where StuNumber in (Select StuNumber from inserted))
update wzY_CreditHour set wzY_CreditHour.CreditHour=IsNull(wzY_CreditHour.CreditHour,0) + wzY_syllabus.CreditHour
from inserted I inner join wzY_syllabus on I.SylNumber=wzY_syllabus.Number
else begin
insert into wzY_CreditHour(StuNumber,CreditHour)
select I.StuNumber,wzY_syllabus.CreditHour
from inserted I inner join wzY_syllabus on I.SylNumber=wzY_syllabus.Number
end
go 要需要当分数大于60的时候才会累加,还有修改当我把分数修改成大于60的时候再次激发学分修改