创建在两个表上运行的触发器 [英] Creating Trigger that runs on two tables

问题描述

我有两个表，COURSE和OFFERING.他们的专栏是:

I have two tables, COURSE and OFFERING. Their columns are:

COURSE (
   courseId, 
   title, 
   cost, 
   duration
) 

和

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

我想配置一个触发器，以确保持续时间为5天(从COURSE表的持续时间列开始)的课程不能在12月(从OFFERING表的startDate列开始)提供.我提出了以下SQL查询:

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.

推荐答案

这很好用.

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;

这篇关于创建在两个表上运行的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！