Coleman University

  SQL and Database Design

   Practical Application 5:

  To your Northwood DB add a DateChanged field for Student, Instructor, and Course tables.
  Add a trigger to the Student, Instructor and Course tables to automatically insert the current date anytime a row is inserted or updated.
  Also define stored procedures, one to select a set of data, one to delete, one to insert and one to update a record.

Stored Procedure code:

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `GetCourse`()
BEGIN
SELECT * FROM NW_Courses END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `AddCourse`
(
IN p_CID varchar(6), IN p_CourseName varchar(40), IN p_Units int(1), IN p_Room int(4), IN p_StartDate date, IN p_EndDate date, IN p_MaxEnroll int(2), IN p_Term varchar(6), IN p_DateChanged timestamp
)
BEGIN
INSERT INTO NW_Course
(
`CID`, `CourseName`, `Units`, `Room`, `StartDate`, `EndDate`, `MaxEnroll`, `Term`, `DateChanged`
)
VALUES
(
p_CID, p_CourseName, p_Units, p_Room, p_StartDate, p_EndDate, p_MaxEnroll, p_Term, p_DateChanged
);
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `UpdateCourseRoom`
(
IN `n_room` INT(4), IN `n_cid` VARCHAR(6)
)
NO SQL
BEGIN
UPDATE NW_Course
SET Room = n_room WHERE CID = n_cid;
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `DelCourse`
(
IN p_CID INT
)
MODIFIES SQL DATA
BEGIN
DELETE FROM NW_Course WHERE CID= p_CID;
END

Previous Page