Search code examples
sqloracle-databaseerwin

Cross-table data validation

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:

DB logical scheme

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?

Solution

  • 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.