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

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `AddStudent`
(
IN p_SID int(4), IN p_StudentName varchar(40), IN p_Address varchar(50), IN p_Phone int(10), IN p_Email varchar(40), IN p_Rank varchar(8), IN p_DOB date, IN p_Has_Advisor tinyint(1), IN p_DateChanged timestamp
)
BEGIN
INSERT INTO NW_Student
(
`SID`, `StudentName`, `Address`, `Phone`, `Email`, `Rank`, `DOB`, `Has_Advisor`, `DateChanged`
)
VALUES
(
p_SID, p_StudentName, p_Address, p_Phone, p_Email, p_Rank, p_DOB, p_Has_Advisor, p_DateChanged
);
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `UpdateStudentAddress`(IN `n_address` VARCHAR(50), IN `n_sid` INT(4))
NO SQL
BEGIN
UPDATE NW_Student
SET address = n_address
WHERE SID = n_sid;
END

CREATE DEFINER=`MyCollections`@`localhost` PROCEDURE `DelStudent`(IN p_SID INT)
MODIFIES SQL DATA
BEGIN
DELETE FROM NW_Student WHERE SID= p_SID;
END

Previous Page    Next Page