trigger pl/sql oracle

[elights]

Buenas, soy nuevo tanto en oracle como en el foro y quisiera saber si me pueden ayudar con el siguiente trigger

en realidad lo que necesito es un trigger que al incertar el valor del semestral(S) en la tabla notas realice las siguientes acciones:

1.- saque el promedio por TIPO DE NOTA, y lo multiplique por el porcentaje respectivo
2.- sume todos los resultados de la operacion anterior para obtener una NOTA TOTAL,
3.- transforme esa NOTA TOTAL, en NOTA FINAL (A,B,C,D O F)
4.- incerte los valores en la tabla TMP

aqui esta la estructura de mi base de datos y el trigger que yo cree:

Tablas:

DISTRIBUCION_DE_NOTAS
TIPO_NOTA CHAR(1),pk (ESTO ES PARA DIFERENCIAR ENTRE P DE PARCIAL, L DE LABORATORIO, Q DE QUIZ Y S DE SEMESTRAL)
DESCRIPCION(VARCHAR2(15)
PORCENTAJE NUMBER(5,2);

ESTUDIANTES
NUMERO NUMBER(5),pk
NOMBRE VARCHAR2(15),
APELLIDO VARCHAR2(15),
CEDULA VARCHAR2(15),
S_SOCIAL VARCHAR2(15),
E_MAIL VARCHAR2(15);

NOTAS
NUMERO NUMBER(5) fk_ESTUDIANTES
TIPO_NOTA CHAR(1), fk_DISTRIBUCION_DE_NOTAS
FECHA DATE,
VALOR NUMBER(5,2), (AQUI VA CUANTO SACO EL ALUMNO EN DICHA EVALUACION)

TMP_ESTUDIANTES_PRO_FINAL
NUMERO NUMBER(5) fk_ESTUDIANTES,
NOMBRE VARCHAR2(15),
APELLIDO VARCHAR2(15),
PRO_FINAL NUMBER(5,2),
NOTA_FINAL CHAR(1) (AQUI SE COLOCA SI LA NOTA FINAL DEL ESTUDIANTE ES A, B, C, D O F)

EL TRIGGER ES

Código SQL:

CREATE OR REPLACE TRIGGER t_nota_final
BEFORE INSERT OR UPDATE ON NOTAS
FOR EACH ROW
WHEN (NEW.TIPO_NOTA='S')
DECLARE
nota_final CHAR (1);
CURSOR c_notas IS
SELECT E.NOMBRE, E.APELLIDO, N.TIPO_NOTA, SUM(AVG(N.VALOR) * D.PORCENTAJE/100) TOTAL_NOTA
FROM ESTUDIANTES E, NOTAS N, DISTRIBUCION_DE_NOTAS D 
WHERE E.NUMERO = :NEW.NUMERO 
AND N.NUMERO = :NEW.NUMERO
AND N.TIPO_NOTA = D.TIPO_NOTA 
GROUP BY E.NOMBRE,E.APELLIDO,N.TIPO_NOTA;
BEGIN
FOR i IN c_notas LOOP
  IF (i.TOTAL_NOTA < 61 ) THEN
    nota_final := 'F';
  ELSIF (i.TOTAL_NOTA < 71 ) THEN
    nota_final := 'D';
  ELSIF (i.TOTAL_NOTA < 81 ) THEN
    nota_final := 'C';
  ELSIF (i.TOTAL_NOTA < 91 ) THEN
    nota_final := 'B';
  ELSE
    nota_final := 'A';    
  END IF;  
INSERT INTO TMP_ESTUDIANTES_PRO_FINAL VALUES
( :NEW.NUMERO, i.NOMBRE, i.APELLIDO, i.TOTAL_NOTA, nota_final);
END LOOP;
END;
 

a pesar de que compila bien, el trigger no funciona.... por favor diganme que hice mal

[thomas_2417]

Para mi punto de vista te hiso falta la union es decir el inner join de cada tabla en el declare por ejemplo:
From estudiantes inner join notas on numero number(5)=numero number(5)
y de igual manera con la nota y la distribucion_de_notas...