How to change a table indicating that data from a certain field can only vary from date 1 to date 2?

Asked

Viewed 38 times

1

I have a table called "LOC_VEICULO" where the vehicle data are contained. I have the following field: DT_FABRICACAO (DATE), where the dates of manufacture of vehicles are stored.

I need to change the vehicles, indicated the manufacturing dates that should vary from 02/01/2008 until 02/01/2010.

How should I proceed?

database oracle date pl-sql timestamp

1 answer

2


To prevent data entries that are not on these dates you can create a contraint in the table, thus:

alter table LOC_VEICULO 
add constraint check_DT_FABRICACAO 
CHECK (DT_FABRICACAO between date'2008-01-02' and date'2010-01-02');

Or create a Rigger that shows you the error:

CREATE OR REPLACE TRIGGER trg_valida_data
BEFORE INSERT OR UPDATE 
ON LOC_VEICULO
FOR EACH ROW
BEGIN
    if (:new.DT_FABRICACAO not between date'2008-01-02' and date'2010-01-02') then
        raise_application_error(-20000,'O valor de Data de fabricação deve variar entre 02/01/2008 e 02/01/2010');
    end if;
END;

/

Browser other questions tagged database oracle date pl-sql timestamp

You are not signed in. Login or sign up in order to post.