IBK IBK - 3 months ago 7
SQL Question

Creating Trigger that runs on two tables

I have two tables,

COURSE
and
OFFERING
. Their columns are:

COURSE (
courseId,
title,
cost,
duration
)


and

OFFERING (
offeringID,
instructor,
startDate,
endDate,
courseId,
locationId
).


I want to configure a trigger that ensures that courses that have duration of 5 days (from duration column of
COURSE
table) cannot be offered in December (from
startDate
column of
OFFERING
table). I came up with the following SQL query:

CREATE OR REPLACE TRIGGER checkDuration
BEFORE INSERT OR UPDATE ON
(course c JOIN offering o
ON
c.courseId = o.courseId)
FOR EACH ROW
BEGIN
IF ((to_char(:new.startDate, 'fmMONTH') = 'DECEMBER') AND duration = 5)
THEN
raise_application_error(-20001, 'Courses of five days duration cannot be run in December');
END IF;
END;


The trigger was created, but with errors.

IBK IBK
Answer

This worked perfectly.

CREATE OR REPLACE TRIGGER checkDuration
  BEFORE INSERT OR UPDATE on offering
  FOR EACH ROW
DECLARE
  isFound NUMBER;
BEGIN
  SELECT 1 INTO isFound FROM DUAL WHERE EXISTS (
    SELECT * FROM Course c
    WHERE c.courseId = :new.courseId AND c.duration = 5);
  IF EXTRACT(MONTH FROM :new.startDate) = 12 
    THEN RAISE_APPLICATION_ERROR(-20001, 'Courses of five days duration cannot be run in December');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;

This worked perfectly.

Comments