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 `GetFaculty`()
BEGIN
SELECT * FROM NW_Faculty;
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `AddFaculty`
(
IN p_FID int(4), IN p_TeacherName varchar(40), IN p_Phone int(10), IN p_Office varchar(40), IN p_Role varchar(10), IN p_Student_Advisor tinyint(1), IN p_DateChanged timestamp
)
BEGIN
INSERT INTO NW_Faculty
(
`FID`, `TeacherName`, `Phone`, `Office`, `Role`, `Student_Advisor`, `DateChanged`
)
VALUES
(
p_FID, p_TeacherName, p_Phone, p_Office, p_Role, p_Student_Advisor, p_DateChanged
);
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `UpdateFacultyOffice`
(
IN `n_office` INT(4), IN `n_fid` INT(4)
)
NO SQL
BEGIN
UPDATE NW_Faculty
SET Office = n_office WHERE FID = n_fid;
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `DelFaculty`
(
IN p_FID INT
)
MODIFIES SQL DATA
BEGIN
DELETE FROM NW_Faculty WHERE FID= p_FID;
END

Previous Page    Next Page