具有聚合功能的PL/SQL触发器 [英] PL/SQL Triggers with aggregate function

问题描述

我有一个触发器，该触发器使用avg()函数根据course表中的课程分数数量来计算学生的gpa.

这里的问题是，当执行触发器时，表中的GPA参数为空.

有人可以看到这个问题吗?

drop table courses;
drop table student;
drop table assignments;

create table student (sid integer, sname char(10), saddress char(10), gpa integer);
create table courses (sid integer, cid integer, cgrade integer);
create table assignments ( sid integer, cid integer, aid integer, agrade integer);


create or replace trigger updategpa before insert or update of cgrade on courses for each row
    begin
        if inserting then
            update student set gpa = (select avg(cgrade) from courses where courses.sid = student.sid) where sid = :new.sid;
        elsif inserting then
            update student set gpa = (select avg(cgrade) from courses where courses.sid = student.sid) where sid = :new.sid;
        elsif deleting then
            delete from student where sid = :new.sid;
        end if;
    end;
/
show errors;


insert into student (sid, sname, saddress, gpa) values (1, 'Mike', 'Brighton', 0);
insert into courses (sid, cid, cgrade) values(1, 2550, 0);
insert into assignments values(1, 2550, 1, 70);
insert into assignments values(1, 2550, 2, 80);

select * from courses;
select * from student;
select * from assignments;

update assignments set agrade = agrade + 5;

select * from courses;
select * from student;
select * from assignments;

解决方案

根据知识，您的触发器似乎是正确的.但是，如果您仔细查看了insert语句.您将在其中插入insert into courses (sid, cid, cgrade) values(1, 2550, 0);，其中cgrade的值为0.因此，显然avg(0)为0并将gpa更新为0.如果您插入几个值，您肯定会在students表中的gpa中更新avg(CGRADE). /p>

尝试另外几个插入进行检查，例如insert into courses (sid, cid, cgrade) values(1, 2551, 10); insert into courses (sid, cid, cgrade) values(1, 2551, 15); insert into courses (sid, cid, cgrade) values(1, 2551, 20);

您肯定会得到结果:

1 Mike Brighton 8

I have a trigger that uses the avg() function to calculate a student's gpa based on the number of course scores that are in the course table.

Problem here is, when my trigger executes, the GPA parameter in the table is empty.

Could anyone see the issue?

drop table courses;
drop table student;
drop table assignments;

create table student (sid integer, sname char(10), saddress char(10), gpa integer);
create table courses (sid integer, cid integer, cgrade integer);
create table assignments ( sid integer, cid integer, aid integer, agrade integer);


create or replace trigger updategpa before insert or update of cgrade on courses for each row
    begin
        if inserting then
            update student set gpa = (select avg(cgrade) from courses where courses.sid = student.sid) where sid = :new.sid;
        elsif inserting then
            update student set gpa = (select avg(cgrade) from courses where courses.sid = student.sid) where sid = :new.sid;
        elsif deleting then
            delete from student where sid = :new.sid;
        end if;
    end;
/
show errors;


insert into student (sid, sname, saddress, gpa) values (1, 'Mike', 'Brighton', 0);
insert into courses (sid, cid, cgrade) values(1, 2550, 0);
insert into assignments values(1, 2550, 1, 70);
insert into assignments values(1, 2550, 2, 80);

select * from courses;
select * from student;
select * from assignments;

update assignments set agrade = agrade + 5;

select * from courses;
select * from student;
select * from assignments;

解决方案

Your trigger seems to be correct as per knowledge. But if you see carefully with your insert statement. You are inserting insert into courses (sid, cid, cgrade) values(1, 2550, 0); here value of cgrade is 0. So obviously avg(0) is 0 and updating gpa as 0. if you insert couple of values you will definitely get the avg(CGRADE) updated in gpa in students table.

try out couple of more inserts to check like insert into courses (sid, cid, cgrade) values(1, 2551, 10); insert into courses (sid, cid, cgrade) values(1, 2551, 15); insert into courses (sid, cid, cgrade) values(1, 2551, 20);

you will definitely get the result:

1 Mike Brighton 8

这篇关于具有聚合功能的PL/SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！