在oracle中创建一个触发器，该触发器使用另一个表中的数据 [英] creating a trigger in oracle that uses data from another table

问题描述

我试图在一个名为race的表中创建一个触发器，该触发器强制执行一个约束条件，即"STARTDATE< = MEETING.ENDDATE"

I am trying to create a trigger in a table called race that enforces a constraint which is "STARTDATE<=MEETING.ENDDATE"

换句话说，竞赛表中的开始日期必须小于或等于会议表的结束日期(此日期之后不能开始运行！)

in other words startdate from the race table must be less than or equals to to the enddate of the meetings table (THE STARTDATE CANNOT BE RUN AFTER THIS DATE!)

我想在向比赛表中添加记录并且输入的起始日期错误(起始日期在会议表格的终止日期之后)时触发触发器

I would like the trigger to fire when adding a record to the race table and the startdate entered is wrong (startdate is after the enddate of the meetings table)

到目前为止，我已经创建了这个，但是我不知道我是对的还是完成的方式！

I have created this so far but I don't know if I'm right or how I finish it!

CREATE OR REPLACE TRIGGER race_date_trg    
BEFORE INSERT OR UPDATE
  ON RACE
  FOR EACH ROW
  WHEN (NEW.RACEID)
   DECLARE STARTDATE DATE;
  BEGIN
    SELECT * FROM meeting INTO VARIABLE
    IF STARTDATE <= ENDDATE THEN
      INSERT INTO RACE (STARTDATE) VALUES (:n.startdate);
    else
  end (race_date_trg);

谢谢您的帮助！

推荐答案

触发器中存在很多错误.

You have a lot of mistakes in your trigger.

1. 您不必声明STARTDATE变量，而应使用:new.STARTDATE
2. 表达WHEN(:new.RACEID)错误
3. 您必须先声明VARIABLE，然后选择它.
4. 您不应将触发器插入Race.如果开始日期不正确，则可能会引发错误.

触发器可能看起来像这样:

The trigger could look like this:

CREATE OR REPLACE TRIGGER race_date_trg    
BEFORE INSERT OR UPDATE
  ON RACE
  FOR EACH ROW
  DECLARE
    MEETING_ENDDATE DATE;
  BEGIN
    SELECT ENDDATE INTO MEETING_ENDDATE FROM meeting;
    IF :NEW.STARTDATE > MEETING_ENDDATE THEN
      RAISE_APPLICATION_ERROR(-20000, 'Wrong start date!');
    END IF:
  end race_date_trg;

这篇关于在oracle中创建一个触发器，该触发器使用另一个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！