Creating Trigger that runs on two tables

sql database oracle triggers oracle10g

42,620

Solution 1

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;

Solution 2

There are no way to link one trigger to two tables unless you create an updatable view which hides both tables map all application code to work with this view. But this solution only useful if you on the start of developing new application from scratch.

If you goal is to keep code only in one place then use a stored procedure or package and call it from each trigger.

create or replace procedure CheckDuration(
  pStartdate in date, 
  pDuration  in number
)
is
begin

  if( (extract(month from pStartDate) = 12) and (pDuration = 5) ) then

    raise_application_error(-20001, 
      'Courses of five days duration cannot be run in December'
    );

  end if;

end;
/

CREATE OR REPLACE TRIGGER course_BIU
BEFORE INSERT OR UPDATE ON course for each row
begin

  for cCheck in (
    select o.StartDate from offering o where o.courseId = :new.courseId
  ) loop
    CheckDuration(cCheck.StartDate, :new.Duration);
  end loop;

end;
/

CREATE OR REPLACE TRIGGER offering_BIU
BEFORE INSERT OR UPDATE ON offering for each row
begin

  for cCheck in (
    select c.Duration from course c where c.courseId = :new.courseId
  ) loop
    CheckDuration(:new.StartDate, cCheck.Duration);
  end loop;

end;

For more generic solution you can pass parameters of course%rowtype and offering%rowtype to stored procedure and perform various checks inside.

42,620

Author by

IBK

Updated on July 05, 2022

Comments

IBK almost 2 years

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 over 10 years

Thanks @ThinkJet. A friend also found another way to do this. Please see the answer below
ThinkJet over 10 years

@IBK Ok. I just focus on the fact that check must be performed if data in any of the tables changed. For example course duration updated to 5 and there are existing offerings for December.
sergioFC almost 7 years

Note that this triggers would allow DB Inconsistencies if the duration of a COURSE is updated to 5.