0

I'm learning Oracle and I had a problem.

Chat table:

CREATE TABLE training
(
   id_training      NUMBER,
   id_user          NUMBER,
   start_training   DATE,
   end_training     DATE
);

I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.

I was thinking of using :old and :new in the trigger, but if I use an insert in my trigger the data of :old always will be null.

How can I overcome this problem of changing :old values, if I'm inserting new values inside my trigger?

0

2 Answers 2

1

Here's a sketch. I get an error from the trigger on db<>fiddle, but you shpould be able to use the idea. First I'll add some constraints on the table, this will simplify things a lot:

CREATE TABLE training
( id_user          NUMBER NOT NULL
, start_training   DATE NOT NULL
, end_training     DATE
,     constraint ak1_training unique (id_user, start_training)
,     constraint c1_training check (start_training <= end_training)
);

I assume id_training is some sort of auto increment number so I left that out since it is not relevant for the idea. Comments:

  • All attributes except end_training is not null
  • is_user and start_training is unique
  • start_training is before or equal to end_training. The constraint is satisfied if end_training is null.

.

CREATE OR REPLACE TRIGGER training_validation
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
    IF EXISTS (
           select 1 from training t
                 where t.id_user = new.user
             and (t.start_training between new.start_training and coalesce(new.end_training, current_date))
                or
                  coalesce(t.start_end, current_date) between new.start_training and coalesce(new.end_training, current_date))
     ) 

  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Some msg ...' );
  END IF;
END training_validation;

I get the error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.

FWIW, I get the same error with the trivial examples I found googling around

Anyhow, the idea is to throw an error if there exists a previous start_training between the new.start_training and new.end_training. Since the new.end_training is typically null, we map that to current_date via coalesce. In the same manner, we check for the previous start_training.

Note that if start_training is not null, we won't have to take possible nulls in consideration. Also since we know that:

start_training <= end_training

we can rely on that as well.

0

Even if you could change the .old and/or .new values in the trigger, what would you change them to?

Presumably the User will have chosen a training event to attend based on their availability. Having the database "magically" change it to some other event when they hit "Save" doesn't make for a User-Friendly experience.

I would argue the best you could do would be to raise an error, throw out the whole transaction and let them pick another event.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.