I'm working on Library Database and it contains a table called book_transaction.
There are 2 columns called issued_date and due_date. due_date should be 7 days from issued_date. Can I specify this condition using default key word while creating the table?
If it is not possible please leave an alternative for the same.
Thank you,
Oracle default constraints cannot refer to other columns. You can get the same functionality using a trigger (see this answer):
CREATE TRIGGER book_transaction_trigger
BEFORE INSERT ON book_transaction
FOR EACH ROW
BEGIN
IF :new.due_date IS NULL THEN
:new.due_date := :new.issued_date + 7;
END IF;
END book_transaction_trigger;
You can add days by adding a number to a date.
You can create a trigger for the table..
CREATE TRIGGER test_trigger BEFORE INSERT ON `book_transaction`
FOR EACH ROW SET NEW.issued_date = IFNULL(NEW.entryDate,NOW()),
NEW.due_date = TIMESTAMPADD(DAY,7,NEW.issued_date)
Thank you for the use full comment by "fabulaspb". I come up with this
create table book_transaction
(
transaction_number int primary key,
book_isbn int REFERENCES book_master(book_isbn),
student_code int references student_master(student_code),
issued_date date default sysdate,
due_date date as (issued_date+7),
submited_date date,
fine int
);
The table is created without an error and it is working fine. Thank you for all.
