创建在两个表上运行的触发器
创建在两个表上运行的触发器
提问于 2013-08-08 23:46:01
我有两张桌子，COURSE和OFFERING。它们的列是：
COURSE (
courseId,
title,
cost,
duration
) 和
OFFERING (
offeringID,
instructor,
startDate,
endDate,
courseId,
locationId
). 我想配置一个触发器，以确保持续时间为5天(来自COURSE表的duration列)的课程不能在12月份提供(来自OFFERING表的startDate列)。我提出了以下SQL查询：
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;触发器已创建，但有错误。
Stack Overflow用户
回答已采纳
发布于 2013-08-09 21:25:32
这样做效果很好。
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18130424
复制相关文章
相似问题
腾讯云开发者
