问题描述

聚合不应出现在 UPDATE 语句的集合列表中。

 create trigger upd_grade on Reports
for update,insert
as
    update Students
    set CreditHours=(select sum(CreditHours) from Courses where Cno in(
    select Cno from Reports where Reports.Grade>=60 and Reports.Sno in(
    select Sno from inserted)))where Students.Sno in(select Sno from inserted)

解决方案

(select sum(CreditHours) from Courses where Cno in(
select Cno from Reports where Reports.Grade>=60 and Reports.Sno in(
select Sno from inserted)))

    定义个参数，接收这个sum的值，然后再update。

解决方案二：

写得太乱了，建议分开写，先把CreditHours查询出来，然后再写更新语句。

解决方案三：

什么数据库，上SQLServer试了试，没问题

 create table Reports(
  Grade int default 0,
  sno varchar(20),
  cno varchar(20)
);
create table Students(
  sno varchar(20),
  CreditHours int  default 0

);
create table Courses(
  cno varchar(20),
  CreditHours int  default 0
)

insert into Students(sno) values('s1');
insert into Courses(cno,CreditHours) values('c1',20);
insert into Courses(cno,CreditHours) values('c2',10);
insert into Reports(Grade,sno,cno) values(60,'s1','c1');--插入这语句不报错

解决方案四：

末尾少掉一个括号。为什么会少的？