CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Alexis Permuy
Alexis Permuy

Reputation: 11

Create table with a restriction for a Reasonable date SQL Oracle

I am here because I'ven having serious issues finding an answer to this question. My doubt is: I have created the following two tables (I omit a few things because I don't think it is useful to write everything here):

SQL> CREATE TABLE PRESTAMO (
num_prestamo NUMBER(9),
fecha_prestamo DATE,
fecha_devolucion DATE, 
tipo VARCHAR(16) CONSTRAINT tipo_nnull NOT NULL, 
CONSTRAINT tipo_ck CHECK (tipo='Prestamo en sala' OR tipo='Prestamo externo')
);

And, this one:

SQL> CREATE TABLE SOLICITA (
num_prestamo NUMBER(9) REFERENCES PRESTAMO(num_prestamo),
isbn NUMBER(9) REFERENCES LIBRO(ISBN),
fecha_solicitud DATE,
);

What I want to do is to set a restriction so that fecha_solicitud<=fecha_prestamo.

Thank you all.

Upvotes: 1

Views: 59

Answers (1)

PKey
PKey

Reputation: 3841

I guess, you could use trigger to do that 

    CREATE OR REPLACE TRIGGER SOLICITA_b4_in_up
    BEFORE INSERT or update 
       ON SOLICITA 
       FOR EACH ROW
    DECLARE
       my_fecha_prestamo date;
    BEGIN  
       select fecha_prestamo into my_fecha_prestamo 
              from PRESTAMO where num_prestamo = :new.num_prestamo;

     if my_fecha_prestamo < :new.fecha_solicitud then 
            Raise_Application_Error (-20050, 'Date constraint violated'); 
     end if;     
    END;
/

Upvotes: 1

Related Questions