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

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) REFERE开发者_如何学运维NCES 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?

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;

In 11g, it is possible to use check constraints with sysdate: http://rwijk.blogspot.com/2007/12/check-constraints-with-sysdate.html

Prior to 11g you should use Justin's approach.

Regards,
Rob.

CONSTRAINT TASKHIST_CHANGEDATE_NONFUTURE CHECK (change_date <= sysdate)

This would be really problematic as a Constraint. Consider what would happen if one were to update a row (some other column) or deactivate/reactive the constraint. It won't check against the original date, but against the current SYSDATE!

I would advocate adding another column to the table, defaulted to SYSDATE, and building a constraint or TRIGGER that will compare the new column (stored SYSDATE) against change_date.

I think that you can define a user-defined function that performs that check and use it in the check constraint.