Oracle database help?

[ohgeetee]

Is this his interpretation or what he was given? many things seem out of place. a class having many teachers seems odd, unless they arent differentiating between multiple english 101's i guess. If this is the case, then the class enrollment table seems out of place, because I assume there would be no reason for a student to have 2 english 101 classes.

[dcasper70]

wporter pm'd me this:
[ QUOTE ]
here are my tables and their primary and foriegn keys.

the first item is the table name - then the PK, then the FKs

students - student_id ce_id, dm_id
class_enrollment - ce_id class_id
class - class_id call_number
course_catalog - call_number
faculty - fac_id
faculty_class - fc_id fac_id, class_id
major - major_id
declared_major - dm_id major_id
department - dept_id major_id


[/ QUOTE ]

I made the diagram directly from the above info.

[wporter]

i need a trigger to fire when the user changes the students grade, and i need it to record the old grade, and the sysdate. do i need to make an audit log table? and then have the records save there? thanks

[IndyGuy]

[ QUOTE ]
i need a trigger to fire when the user changes the students grade, and i need it to record the old grade, and the sysdate. do i need to make an audit log table? and then have the records save there? thanks

[/ QUOTE ]

Yes. Assuming you still need help with this, even though it's a few days old...

From what's posted above, you'll create a trigger for update of the (for example) course_roster table (or wherever you're storing the grades, as it's not clear from above). In the trigger, you'll simply have a in insert statement into a log table.
The following statement does that, although it obviously depends on the structure of your tables. Note the use of the :OLD and :NEW variables to refer to the changing values.

create or replace trigger TRIG_GRADES_LOG
after update on COURSE_ROSTER
for each row
declare
begin
insert into COURSE_ROSTER_LOG(course_no, student_id, old_grade, new_grade, change_date) values (:OLD.course_no, :OLD.student_id, :OLD.grade, :NEW.grade, sysdate);
end;


Normally, I'd not do someone's homework for them, but I felt like an Oracle refresher would be a nice change of pace for me.

[wporter]

thanks alot, that really helps. i think i see where this trigger is going now.