CODEWITHSUNDEEP
oracle-databaseplsqltriggers
mhammad mhammad
mhammad mhammad

Reputation: 13

Create Trigger with Condition

I am trying to create a trigger which inserts into the ALARMS table the values student_id and course_id when a student registers on a course without its prerequisite, but I failed.

This is my trigger:

CREATE OR REPLACE TRIGGER ALARM_TRIGGER
BEFORE INSERT OR UPDATE OF student_id, course_id ON students_courses
FOR EACH ROW

BEGIN
  insert into alarms(student_id, course_id) values(:NEW.student_id, :NEW.course_id);
END;

The Tables:

1-COURSES: which have course_id (PK) and course_name.

2-STUDENTS: which have student_id (PK) and student_name.

3-STUDENTS_COURSES: which have each student with his courses.

4-PREREQUISITE_COURSES: which have prerequisite each course.

5-ALARMS: Trigger which store student_id, course_id when any student register in course without its prerequisite.

This is example of courses prerequisite table:

COURSE_NUMBER             COURSE_PREREQUISITE
  1                        null
  2                         1
  3                        null
  4                        3

Upvotes: 1

Views: 519

Answers (1)

TommCatt
TommCatt

Reputation: 5636

If a student enrolls in a course without a prerequisite, the result of querying for prerequisites will be NULL. So, if the course has a prerequisite and the student has taken the prerequisite, you want the result to be the same. That makes subsequent logic easier. So if the course has a prerequisite the student has not taken, the result will be the course id of the prerequisite (you can use that to display a meaningful error message).

The query to return NULL if the course has no prerequisite is easy:

select P.COURSE_PREREQUISITE Result
into   RequiredReq
from   Prerequisites P
where  P.COURSE_NUMBER = :new.course_id;

Of course, it returns the course id of the prerequisite if there is one, but we'll take care of that later. Now we want to join with the STUDENTS_COURSES table:

join   STUDENTS_COURSES SC
    on SC.COURSE_NUMBER = P.COURSE_PREREQUISITE
   and SC.Student_ID    = :new.Student_ID

However, this is not going to give us everything we need. First, if the student has not taken the prerequisite, the query will return nothing so we'll get a NO_DATA_FOUND error. Second, if the student has taken the prerequisite, it will return the course id but in this case we want it to return a NULL.

Let's see what we get if we turn the inner join to a left outer join:

select P.COURSE_PREREQUISITE Result
into   RequiredReq
from   Prerequisites P
left join STUDENTS_COURSES SC
    on SC.COURSE_NUMBER = P.COURSE_PREREQUISITE
   and SC.Student_ID    = :new.Student_ID
where  P.COURSE_NUMBER  = :new.course_id;

This gives us everything we want, but not quite in the right way. We get NULL if the prerequisite has not been met and the prerequisite value if it has. So to change it around, we just need a little logic in the query:

select case when P.Prerequisite is null then null    --> No prerequisite
            when SC.StudentID is not null then null  --> Prerequisite met
            else P.Prerequisite end Result           --> Prerequisite not met
into   RequiredReq
from   Prerequisites P
left join STUDENTS_COURSES SC
    on SC.COURSE_NUMBER = P.COURSE_PREREQUISITE
   and SC.Student_ID    = :new.Student_ID
where  P.COURSE_NUMBER  = :new.course_id;

Now a NULL means either the course has no prerequisite or the student has met the prerequisite. Only if the course has a prerequisite and the student has not met it is a value returned and that is the course number of the unmet prerequisite.

Note: this works only if, as implied in your example data, there is only one prerequisite per course. If there can be one or more prerequisites, you will probably want to change to numeric returns with 0 meaning no or all met prerequisites and non-zero meaning the number of unmet prerequisites.

Upvotes: 1

Related Questions