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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With