Oracle database help?

[wporter]

i am currently working on a project in one of my classes that deals with building a database using oracle, is anyone familiar with this? i need some help with some things, let me know. thanks alot!

[dcasper70]

Whatcha need? Details please.

I've done a good amount of oracle stuff, but it's a such a huge beast that I may be only able to point you in a direction....

[wporter]

yeah im just doing a project thats pretty simple. we just have like 9 tables, and gotta have them connected by PK's and FK's, we also need some sequences and triggers. i just cant figure out what the point of a sequence is, and im havin some problems with the trigger. think you could help?

[dcasper70]

A sequence is actually pretty handy.
Say you have a unique ID field, let's call it Emp_Num (employee number), and every time you insert a new employee you want to give them the next ID number.
Instead of getting the Max(Emp_Num) + 1 in the employee table, just create a sequence. It will keep an incremental list for you.

So an insert can be
Insert into Employee (EMP_NUM, F_NAME, L_NAME)
Values
seqEmployee.NextVal,'Willy','Porter';

Or something like that.


What are you doing with the trigger?

[wporter]

im still trying to figure it out. this is my first database class, and this is our first example. we have a college situation where we have 9 tables, students, departments, faculty, etc. he wants us to have students and classes and to be able to show student schedules. he wants sequences and triggers. the trigger has an audit log which keeps track of the old grade, new grade, user, and sysdate. i have somewhat of an idea on the code for the trigger, but im really confused about some of the simple stuff like which FK should go where, and im not sure my ERD model is correct either. does this sound simple to u?

[dcasper70]

[ QUOTE ]
does this sound simple to u?

[/ QUOTE ]
yes

Hey, your an Oracle noob. Gotta start somewhere...


Feel free to pm me or post some more specific stuff.

[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.