IBK IBK - 1 year ago 55
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 Source

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.