I'm new to sql and I do not know how to address this problem.
I have a table that stores citations with a atributo hora of type varchar2 .
I need a trigger that avoid the insertion and update of hours that are not in the middle point, that is to say that there are only appointments every half hour.
I have no idea how to do it, I hope you could help me.
Thanks
Validation can be done in the following way.
For tests create a table:
-- Create table
create table TIME_TEST
(
HORA DATE
);
Then create the following trigger that uses the EXTRACT to get the minute of the hour in numeric format:
create or replace trigger hora_tests
BEFORE INSERT OR UPDATE ON time_test
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW
declare
begin
IF EXTRACT(MINUTE FROM CAST(:N.HORA as TIMESTAMP)) NOT IN (0,30) THEN
Raise_Application_Error (-20343, 'Debe ser en punto o y media.');
END IF;
end hora_tests;
Validation performance tests:
SQL> insert into time_test values(to_date('01/08/2017 12:00', 'DD/MM/YYYY HH24:MI'));
1 row inserted
SQL> insert into time_test values(to_date('01/08/2017 12:01', 'DD/MM/YYYY HH24:MI'));
insert into time_test values(to_date('01/08/2017 12:01', 'DD/MM/YYYY HH24:MI'))
ORA-20343: Debe ser en punto o y media.
ORA-06512: en "HORA_TESTS", línea 4
ORA-04088: error durante la ejecución del disparador 'HORA_TESTS'
SQL> insert into time_test values(to_date('01/08/2017 12:30', 'DD/MM/YYYY HH24:MI'));
1 row inserted
SQL>
The other way, something simpler, is to verify using a CONSTRAINT table as follows:
CREATE TABLE Ejemplo
(
Hora DATE,
CHECK (EXTRACT(MINUTE FROM CAST(Hora as TIMESTAMP)) IN (0,30))
);
By doing this:
INSERT INTO Ejemplo(Hora)
VALUES (to_date('01/08/2017 12:01', 'DD/MM/YYYY HH24:MI'));
The error will be something like this:
ORA-02290: check constraint (USER_4_AB04F.SYS_C007366) violated