A3.2 Insert CAMPUS_PLACES Validator

To protect against entering an invalid (campus, place) into CAMPUS_PLACES, we will create a "before insert" trigger for CAMPUS_PLACES named CAMPUS_PLACES_VALIDATOR. This trigger will only allow the insert of (campus, place) into CAMPUS_PLACES if there is not already a place designated as the favorite for that cuisine at that campus.

A trigger can abort an INSERT, UPDATE or DELETE using the following code:

      SET @msg = 'Error message ..';

      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;

The error message is generated then the SIGNAL statement aborts the insertion and returns the error message.

The trigger accepts parameters NEW.CAMPUS and NEW.PLACE. The trigger should first determine the cuisine of NEW.PLACE. The insert aborts if NEW.PLACE's cuisine is unknown (which would imply NEW.PLACE is not in PLACES_CUISINES).

Once you know the cuisine of NEW.PLACE, the trigger must check if NEW.CAMPUS already has a favorite place of that cuisine. If it does, the insert is aborted. Watch this video if you need help.

A good test of your trigger is to attempt to insert Big City Diner Waialae into CAMPUS_PLACES as a favorite place for UHM assuming UHM already has a favorite Burgers place.