Check constraint for making sure a date is not in the future?

Question

I'm trying to create a table that records changes made to the estimated hours of another table. The database as a whole is a project management system for a company to assign work to its employees and create invoices for the customer.

Currently I have:

CREATE TABLE task_history
(
    task_history_id         NUMBER(5),
    previous_est_hours      NUMBER(3,1),
    change_date         DATE,
    reason_for_change       VARCHAR2(50),
    task_id             NUMBER(5),

CONSTRAINT TASKHIST_TASKHISTID_PK   PRIMARY KEY (task_history_id),
CONSTRAINT TASKHIST_TASKID_FK       FOREIGN KEY (task_id) REFERENCES task(task_id),
CONSTRAINT TASKHIST_TASKID_NN CHECK (task_id IS NOT NULL),
CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate)
);

change_date must not be a future date, it must be either today or in the past. The last check constraint is the problem. As I understand it you cannot use the sysdate because of a reason I've forgotten, but you can't. I've also tried GETDATE() and every other variant I've found online. How can I do this presumably simple task?

Answer 1

You can't call a function from a check constraint so the most natural approach would be to define a trigger on the task_history table, i.e.

CREATE OR REPLACE TRIGGER task_change_date_in_past
  BEFORE INSERT OR UPDATE ON task_history
  FOR EACH ROW
BEGIN
  IF( :new.change_date > sysdate )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Change date must be in the past' );
  END IF;
END;