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.

Trigger code:

CREATE TRIGGER `Add New Student` BEFORE INSERT ON `NW_Student` FOR EACH ROW SET New.DateChanged = NOW();
CREATE TRIGGER `Update Student Info` BEFORE UPDATE ON `NW_Student` FOR EACH ROW SET New.DateChanged = NOW();
CREATE TRIGGER `Add New Faculty` BEFORE INSERT ON `NW_Faculty` FOR EACH ROW SET New.DateChanged = NOW();
CREATE TRIGGER `Update Faculty Info` BEFORE UPDATE ON `NW_Faculty` FOR EACH ROW SET New.DateChanged = NOW();
CREATE TRIGGER `Add New Course` BEFORE INSERT ON `NW_Course` FOR EACH ROW SET New.DateChanged = NOW();
CREATE TRIGGER `Update Course Info` BEFORE UPDATE ON `NW_Course` FOR EACH ROW SET New.DateChanged = NOW();

Next Page