SQL Trigger to Update a Table Row

By suzmarie10 ·
I am creating a database to store records about classroom changes at the university level. Here is my database schema as of yet:

Courses (sessionCode, deptNo, courseNo, sectionNo, blgd, roomNo, name, maxEnroll, noEnrolled)

CourseInstructors (sessionCode, deptNo, courseNo, sectionNo, instructorID)

Rooms ( bldg, roomNo, maxCapacity)

CourseDetails (sessionCode, deptNo, courseNo, sectionNo, bldg, roomNo, day, timeStart, timeEnd, reqID)

Faculty (ID, name, deptNo?)

ReqChange (sessionCode, deptNo, courseNo, sectionNo, reqID , bldg, roomNo, day, timeStart, dateEntered, status, ID)

ReqProcedures (sessionCode, deptNo, courseNo, sectionNo, reqID, bldg, roomNo, day, startTime, proceduresComplete)

ReqReasons (sessionCode, deptNo, courseNo, sectionNo, reqID, reason)

ReqComments (sessionCode, deptNo, courseNo, sectionNo, reqID, comment)

I am wondering if there is a way to constantly update the courses table to only hold one value for each course and that value being the row in reqChange that has the most recent dateEntered and a complete status. Can i use a trigger here? Thanks

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

The row ?

by Tony Hopkinson In reply to SQL Trigger to Update a T ...

Well if you gave ReqChange a ChangeID column that was unique and then added that to Courses (which actually appears to Sessions By the way...) Then you could set up a trigger on inert (and update ?)

What if the inserted record isn't the latest one.
What if it get's deleted, what if it was updates and was now the latest or no longer the latest. You could check all this stuff of course. make the trigger more compex take longer..

Or you could just query out the answer when you need it with a join of Courses and ReqChange and a HAVING clause.

Is there some reason for all the duplication by the way? I would have expected Dept an Section for instance to be tied to course or session. You schema implies Session A from CourseNo 1, could be related to more than one department.

Related Discussions

Related Forums