Consider the final term until the next day at 03 am using PLSQL

Asked

Viewed 204 times

3

Guys, I need to do the following validation:
When a record enters my database, I need to validate the effective date of this record. If the term is between the current date then the record is valid.
Ex:
Duration from 03/12/2015 to 03/13/2015, the duration ends on 03/13/2015?
But I need to consider the final date until the day 14/03/2015 at 03:00 am, after this time the final date will no longer valid.
Does anyone have any idea how I can create this rule?

pl-sql oracle11c

  • Details of type are missing, which structure of the tables involved ? Which TYPES ? Which field is validated against what ? In theory a CHECK CONSTRAINT or a TRIGGER may solve the problem, but details are missing for an answer.

  • Would you need the structure? It’s just a rule. Types are dates. In the case the basis is the final effective date, I need it to be considered as true until 03:00 the morning of the next day!

  • The value that will enter must be between the correct date and the next date (with the 3h) right ? Today would be 13/03/2015 03:01 and 14/03/2015 03:00 , if for , try a validation Trigger that makes this rule.

  • Yes, it’s just that in the case my doubt is not what object I use to do, in my case it’s how I would even consider it. I edited the description of the problem, I think it’s better now!

1 answer

1

Something like that

CREATE OR REPLACE TRIGGER TRG_TABELA
  BEFORE
    INSERT OR
    UPDATE 
  ON TABELA
BEGIN
  /*3 horas de "hoje" até 3 horas de "amanhã"*/
  IF NOT (DATA BETWEEN (TRUNC(SYSDATE) + 3/24) AND TRUNC(SYSDATE+1) + 3/24)) THEN
    RAISE_APPLICATION_ERROR(-20001,'DATA INVÁLIDA PARA PERÍODO');
  END IF;
END;
/

Browser other questions tagged pl-sql oracle11c

You are not signed in. Login or sign up in order to post.