need your help
I have 2 connected entities: library reader (subscriber) and book issues. Reader can be locked for some period in days because of breaking library rules:
I need to add check so that reader, who's lock period isn't ended yet, couldn't take a book in a library (in other words, Issue.Taken > Subscriber.Lock_Date+Subscriber.Lock_Period)
Please help, how can I do this?
This should really be handled in the business logic and not at the table level.
However, you could do it using a materialized view:
CREATE TABLE subscriber (
id INT PRIMARY KEY,
lock_date DATE,
lock_period INTERVAL DAY(5) TO SECOND
);
CREATE TABLE issue (
id INT PRIMARY KEY,
subscr_id INT NOT NULL REFERENCES subscriber( id ),
book_id INT,
taken DATE,
returned DATE
);
CREATE MATERIALIZED VIEW LOG ON subscriber
WITH SEQUENCE, ROWID( id, lock_date, lock_period )
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON issue
WITH SEQUENCE, ROWID( subscr_id, taken )
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW subscriber_issue_MV
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT s.id,
s.lock_date,
s.lock_period,
i.taken
FROM subscriber s
INNER JOIN
issue i
ON ( i.subscr_id = s.id );
ALTER TABLE subscriber_issue_MV ADD CONSTRAINT subscriber_issue__mv__chk
CHECK ( lock_date IS NULL
OR lock_period IS NULL
OR NOT taken BETWEEN lock_date AND lock_date + lock_period );
I agree with MTO that such validations should be handled by application code (via a stored procedure). However, if you are insistent about performing this validation via the database then the following trigger will be helpful. Again, I do not recommend this solution and the best way would be to handle it using application logic.
CREATE OR REPLACE TRIGGER trg_val_lock_dt
BEFORE INSERT ON issue
FOR EACH ROW
DECLARE
v_is_valid CHAR(1);
BEGIN
v_is_valid := 'Y';
SELECT 'N' INTO v_is_valid
FROM subscriber s
WHERE :NEW.subscr_id = s.subscr_id
AND :NEW.taken BETWEEN s.lock_date AND (s.lock_date + lock_period);
RAISE_APPLICATION_ERROR(-20001,'The subscriber is locked');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
The above trigger will fire before every insert in the issue table. It will check if the taken date falls between the lock date and lock date + lock period (which would be the lock end date). If such a record is found then it will throw the following error and the row will not be inserted.
ORA-20001: The subscriber is locked
ORA-06512: at "RETAIL_1.TRG_VAL_LOCK_DT", line 12
If the condition is not satisfied then the no data found exception will be raised where the trigger will do nothing and the row will be inserted.
