Using a trigger to implement a foreign key check c

Question

I'm trying to implement this constraint into my database: (In the table Race) CONSTRAINT (Date <= Meeting.EndDate)

Essentially the column in the Race Table needs to be Less than the EndDate column in the Meeting table.

Pretty sure I need to use a trigger to implement a foreign key however, I'm not that sure how I would go about implementing one. So Far, All I have is:

CREATE OR REPLACE TRIGGER RaceDateCheck
AFTER UPDATE OF Race ON StartDateCheck
BEGIN
 INSERT INTO Race
    (MeetingEndDate)
 SELECT EndDate FROM Meeting
END;

Thanks,

Answer 1

Presumably the race date has to be between the meeting start and end date, so you could check both at once; and also presumably you want to check this for new records, not just updates. So you could use something like:

CREATE OR REPLACE TRIGGER RaceDateCheck
BEFORE INSERT OR UPDATE ON Race
FOR EACH ROW
DECLARE
  meetingStart Meeting.MeetingStartDate%TYPE;
  meetingEnd Meeting.MeetingEndDate%TYPE;
BEGIN
  SELECT StartDate, EndDate
  INTO meetingStart, meetingEnd
  FROM Meeting
  WHERE MeetingID = :NEW.MeetingID;

  IF :NEW.RaceDate < meetingStart
    OR :NEW.RaceDate > meetingEnd THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid race date');
  END IF;
END;

For just the end date:

CREATE OR REPLACE TRIGGER RaceDateCheck
BEFORE INSERT OR UPDATE ON Race
FOR EACH ROW
DECLARE
  meetingEnd Meeting.MeetingEndDate%TYPE;
BEGIN
  SELECT EndDate
  INTO meetingEnd
  FROM Meeting
  WHERE MeetingID = :NEW.MeetingID;

  IF :NEW.RaceDate > meetingEnd THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid race date');
  END IF;
END;