ALTER TABLE student ADD gpa NUMBER;
CREATE OR REPLACE TRIGGER "USER36401"."GETGPA" AFTER
DELETE OR
INSERT OR
UPDATE ON grade_report FOR EACH row DECLARE totalqp NUMBER :=0;
totalgpa NUMBER :=0;
totalcreds NUMBER :=0;
prevqp NUMBER :=0;
prevgpa NUMBER :=0;
prevcreds NUMBER :=0;
incomingqp NUMBER :=0;
incominggpa NUMBER :=0;
incomingcreds NUMBER :=0;
temp NUMBER :=0;
BEGIN
CASE
WHEN updating THEN
UPDATE student SET student.gpa = NVL(student.gpa,0);
SELECT student.gpa
INTO temp
FROM student
WHERE student.student_number = :NEW.student_Number;
IF temp <1 THEN
totalqp := temp;
SELECT DISTINCT DECODE(:NEW.GRADE,'A','4','B','3','C','2','D','1','F','0')
INTO temp
FROM student,
section,
course
WHERE :NEW.student_number = student.student_number
AND :NEW.section_identifier = section.section_identifier
AND section.course_number = course.course_number;
UPDATE student
SET student.gpa = temp
WHERE student.student_number = :NEW.student_number;
ELSE
prevgpa := temp;
SELECT student.total_credit_hours
INTO temp
FROM student
WHERE student.student_number = :NEW.student_number;
prevcreds := temp;
SELECT DISTINCT course.credit_hours
INTO temp
FROM course,
section
WHERE course.course_number = section.course_number
AND section.section_identifier= :NEW.section_identifier;
--current
SELECT DISTINCT course.credit_hours
INTO temp
FROM course,
section
WHERE course.course_number = section.course_number
AND section.section_identifier= :NEW.section_identifier;
incomingcreds := temp;
prevcreds := prevcreds - incomingcreds;
prevqp := prevgpa * prevcreds;
--total quality points before add
SELECT DECODE(:NEW.GRADE,'A','4','B','3','C','2','D','1','F','0')
INTO temp
FROM student,
section,
course
WHERE :NEW.student_number = student.student_number
AND :NEW.section_identifier = section.section_identifier
AND section.course_number = course.course_number;
incominggpa := temp;
--gpa being added before quality points
SELECT DISTINCT course.credit_hours
INTO temp
FROM course,
section
WHERE course.course_number = section.course_number
AND section.section_identifier= :NEW.section_identifier;
incomingqp := incominggpa*incomingcreds;
totalqp := prevqp + incomingqp;
totalcreds := prevcreds + incomingcreds;
totalgpa := totalqp / totalcreds;
UPDATE student
SET gpa = totalgpa
WHERE student.student_number = :NEW.student_number;
END IF;
WHEN DELETING then
Select * from student where student.student_number = :NEW.student_number;
END CASE;
END;
the goal is for the trigger to do "something [not implemented yet]" on delete.
when i compile, it says compile, but warning with errors. i'm burned out as hell for finals week, and this is the last project. i can't find the error where it's at, i'm sure it's something syntactical that i'm not spotting. any help is greatly appreciated.
edit:also, i understand this is probably the most ineffecient way to implement what i'm trying to do. but i'm basing my code entirely off of what the professor has listed we can use in the powerpoint slides in class. and quite frankly, this is for my minor specialization, and i'm a senior, so i don't really care. i just need it to work.
edit3:
from the compile trigger script: ALTER TABLE student succeeded. Warning: execution completed with warning TRIGGER "USER36401"."GETGPA" Compiled.
insert into grade_report values ('17','112','B');
select * from student;
insert into grade_report values ('17','119','C');
select * from student;
insert into grade_report values ('8','85','A');
select * from student;
insert into grade_report values ('8','92','A');
select * from student;
insert into grade_report values ('8','102','B');
select * from student;
insert into grade_report values ('8','135','A');
returns error: "Error starting at line 1 in command: insert into grade_report values ('17','112','B') Error at Command Line:1 Column:12 Error report: SQL Error: ORA-04098: trigger 'USER36401.GETGPA' is invalid and failed re-validation 04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation" *Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger. *Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger."
