creating a trigger in oracle that uses data from another table

Question

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

Thank-you for your help!

There are several syntax errors in your code. Perhaps this document will be helpful. — Yaroslav Shabalin
– Yaroslav Shabalin, Commented Feb 25, 2014 at 12:17

Bay73 · Accepted Answer · 2014-02-26 11:49:39Z

1

You have a lot of mistakes in your trigger.

You don't have to declare STARTDATE variable, you should use :new.STARTDATE
Expression WHEN (:new.RACEID) is wrong
You must declare VARIABLE before selecting to it.
Your trigger shouldn't insert into Race. You could raise an error if start date is incorrect.

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;

answered Feb 26, 2014 at 11:49

Bay73

1262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating a trigger in oracle that uses data from another table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related