Using date in a check constraint, Oracle

by

A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.

CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF( :new.CloseDate <= SYSDATE )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
          'Invalid CloseDate: CloseDate must be greater than the current date - value=" || 
          to_char( :new.CloseDate, "YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.CloseDate > add_months(SYSDATE,12) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
         'Invalid CloseDate: CloseDate must be within the next year - value=" || 
         to_char( :new.CloseDate, "YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.StartDate <= add_months(:new.CloseDate,24) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
          'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate=" || 
          to_char( :new.StartDate, "YYYY-MM-DD HH24:MI:SS' ) ||
          ' CloseDate=" || to_char( :new.CloseDate , "YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
END;

More Related Contents:

  1. How are dates stored in Oracle?
  2. Formatting DATE in oracle
  3. Convert epoch to date in sqlplus / Oracle
  4. Oracle: how to add minutes to a timestamp?
  5. Valid Date Checks in Oracle
  6. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  7. How to get the week day name from a date?
  8. How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?
  9. Current date in Oracle Db
  10. Is java.sql.Timestamp timezone specific?
  11. How to uninstall / completely remove Oracle 11g (client)?
  12. Get counts of all tables in a schema
  13. oracle convert unix epoch time to date
  14. How to export query result to csv in Oracle SQL Developer?
  15. Oracle JDBC ojdbc6 Jar as a Maven Dependency
  16. ORA-01882: timezone region not found
  17. how to export data from log table to email body in oracle
  18. Convert timestamp datatype into unix timestamp Oracle
  19. Get month name from date in Oracle
  20. How to get primary key column in Oracle?
  21. How to create a user in Oracle 11g and grant permissions
  22. Trigger to enforce M-M relationship
  23. Comparing with date in Oracle sql
  24. How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
  25. Functions vs procedures in Oracle
  26. Subtracting Dates in Oracle – Number or Interval Datatype?
  27. DBMS_JOB vs DBMS_SCHEDULER
  28. How to change default nls_date_format for oracle jdbc client
  29. How to generate a GUID in Oracle?
  30. Escaping single quote in PLSQL

Leave a Comment