Creating a trigger that runs on two tables

Question

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 set up a trigger that ensures that courses lasting 5 days (from the COURSE table duration column) cannot be offered in December (from the startDate column of the startDate 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; 

A trigger was created, but with errors.

Answer 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; 

This worked perfectly.

Answer 2

There is no way to associate one trigger with two tables unless you create an updatable view that hides both tables in order to match all the application code to work with that view. But this solution is only useful if you start developing a new application from scratch.

If you want to save the code in only one place, 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 a more general solution, you can pass the course%rowtype and offering%rowtype to the stored procedure and perform various checks inside.