error trying to make a trigger in oracle

Question

Hello, i'm trying to make a TRIGGER in ORACLE using ORACLE SQL DEVELOPER, I would check that the dates entered as start date reservation to reservation date or date of a service charge will be equal to or greater than the current date is inserted in the record.

these are the fields in my table

Service (date_service, cost_variation, number_room, id_service);

this is my code:

CREATE OR REPLACE TRIGGER VERIFY_DATE
BEFORE INSERT OR UPDATE OF FECHA_PLAN ON SERVICE
FOR EACH ROW
DECLARE
  fecha_ac DATE;
BEGIN
  SELECT SYSDATE INTO fecha_ac FROM DUAL;
  IF(:NEW.FECHA_PLAN > fecha_ac)THEN
    dbms_output.put_line('The date of the plan should be more than the current date ');
    raise_application_error(-20601, 'Dato invalido');
  END IF;
END;

and this is the error I get when trying to run the TRIGGER

INSERT INTO "MIGRARBD"."SERVICE" 
    (date_service, cost_variation, number_room, id_service) 
VALUES 
    (TO_DATE('20/01/10', 'DD/MM/RR'), '2', '1', '1')

There was an error when saving changes to table "MIGRARBD"."SERVICE":

Fila 1: ORA-20601: Dato invalido
ORA-06512: en "MIGRARBD.VERIFICAR_FECHA", línea 7
ORA-04088: error during execution of trigger 'MIGRARBD.VERIFICAR_FECHA'
ORA-06512: on line 1

I hope you can help ... and excuse my English

Answer 1

Your trigger references FECHA_PLAN which doesn't match how you have defined the table. Presumably it is the same as DATE_SERVICE.

As you have coded it, the trigger fails if the entered date is greater than the current date. But when you say ...

I would check that the dates entered ... will be equal to or greater than the current date is inserted in the record.

... perhaps what you want is to enforce a rule that the date must be greater or equal to the current date. If so, your trigger ought to fail if the entered date is less than the current date. Like this ...

IF(:NEW.FECHA_PLAN < sysdate )THEN

Note that we can use sysdate directly, so the select ... from dual is unnecessary (unless as Rene points out in the comments you want to use the same value multiple times).

If this doesn't address your problem you will need to explain a bit more.

Answer 2

Isn't your date comparison backwards to what you stated you wanted? You're raising an error if the date inserted is greater than SYSDATE. Shouldn't you test for SYSDATE greater than the date inserted in new record? Try this:

  IF(fecha_ac >= :NEW.FECHA_PLAN)THEN
    dbms_output.put_line('The date of the plan should be more than the current date ');
    raise_application_error(-20601, 'Dato invalido');
  END IF;

Answer 3

If the trigger raises your custom 20601 error, it means that the trigger is compiled successfully and works, and the problem resides in your logic. In your question it is not clear enough if you want to disallow past or future dates. In other words: if fecha plan must be future the operator is wrong. If it must be a past date then it's fine.

With that said, it's not a good idea to put business logic in triggers.