CODEWITHSUNDEEP
sqloracle-databaseerwin
JGDger
JGDger

Reputation: 105

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?

Upvotes: 0

Views: 849

Answers (2)

phonetic_man
phonetic_man

Reputation: 1088

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.

Upvotes: 1

MT0
MT0

Reputation: 168588

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 );

Upvotes: 3

Related Questions