CODEWITHSUNDEEP
oracle-databaseplsqloracle11gdatabase-trigger
Bobrek
Bobrek

Reputation: 23

Inserting data following cursor creation with restricted selection oracle

This is my first time posting on Stack Overflow. I normally just search previous questions and find my answers but in this instance I can't seem to find the answer to my question either here or on the wider internet.

The issue I have is that I want to populate a many-to-many link table used for storing attendance automatically. When a new student is added to a course, the students StudentNo and each applicable Lesson's LessonNo will be entered into the link table as primary keys. In addition, the student's attendance code will automatically be set to 'T' to show that the attendance is To Be Confirmed.

I don't want this to be a procedure as the database expects upwards of 6,000 active studentNo entries, with over a hundred lessons being applicable to each student. Only home students (their definition, not mine) attend the college. The homeStudent primary key is identical to the overall student primary key.

My trigger is as follows:

CREATE OR REPLACE TRIGGER Student_Added_to_Course
AFTER INSERT ON HomeStudent
FOR EACH ROW
DECLARE 
CURSOR applicable_lessons
IS
   Select Student.studentNo, Lesson.LessonNo
   FROM
   Student, Lesson, Course, Module
   WHERE
   Student.StudentNo = :new.studentNo
   AND
   Student.CourseNo = Course.CourseNo
   AND
   Course.CourseNo = Module.CourseNo
   AND
   Lesson.ModuleNo = Module.ModuleNo;
BEGIN
 FOR I IN applicable_lessons
 LOOP
 INSERT INTO Attendance (StudentNo, LessonNo, AttendanceCode)
 VALUES (I.student.studentNo, I.Lesson.LessonNo, 'T');
  END LOOP;
END;
/

I am receiving the following errors:

Line 18: SQL statement ignored Line 19: component 'Lesson' must be declared Line 19: column not allowed here

Since all my errors are occurring from line 18 onwards I assume I have made a mistake during my insertion loop?

Could anybody tell me what I'm doing wrong here. I assume the fix is fairly straightforward but I'm at the point of going a bit cross-eyed looking at my own work and can't find the fault.

Thank you in advance, Bobrek

Upvotes: 2

Views: 43

Answers (2)

Boneist
Boneist

Reputation: 23588

Why loop over a cursor when you can just do a single insert statement? That will be faster!

E.g.:

CREATE OR REPLACE TRIGGER Student_Added_to_Course
AFTER INSERT ON HomeStudent
FOR EACH ROW
BEGIN

  INSERT INTO attendance (studentno, lessonno, attendancecode)
  SELECT s.studentno,
         l.lessonno,
         'T'
  FROM   student s
         INNER JOIN course c ON s.courseno = c.courseno
         INNER JOIN module m ON c.courseno = m.courseno
         INNER JOIN lesson l ON m.moduleno = l.moduleno
  WHERE  s.studentno = :new.studentno;

END Student_Added_to_Course;
/

Note that I have changed your join syntax to use ANSI join syntax, and note how it's much easier to see how the tables are linked.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143053

If you change INSERT statement to

INSERT INTO Attendance (StudentNo, LessonNo, AttendanceCode)
VALUES (I.studentNo, I.LessonNo, 'T');

you should be good.

Because, cursor variable is i and cursor selects studentno and lessonno columns; it doesn't matter (any more) which table they belong to - you reference them by cursor variable's name, i.e. i.studentno and i.lesonno.

Upvotes: 1

Related Questions