By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T_SCHEDULE
(
sched_id number(4) NOT NULL,
master_id number(10),
client_id number(10),
box_num number(5),
car_num varchar2(10),
price number(7) NOT NULL,
job_start timestamp NOT NULL,
job_stop timestamp NOT NULL,
PRIMARY KEY(sched_id)
);
CREATE OR REPLACE TRIGGER HOURS_A_DAY
BEFORE INSERT OR UPDATE ON T_SCHEDULE
FOR EACH ROW
DECLARE
v_hours number := 0;
BEGIN
SELECT SUM(CAST(job_stop AS DATE) - CAST(job_start AS DATE)) * 24
INTO v_hours
FROM T_SCHEDULE
WHERE job_start >= TRUNC(:NEW.job_start)
AND job_start < TRUNC(:NEW.job_start) + INTERVAL '1' DAY
AND master_id = :NEW.master_id;
IF v_hours > 8 THEN
RAISE_APPLICATION_ERROR(
-20000,
'Cannot schedule for more than 8 hours in a day.'
);
END IF;
END;
/
SELECT * FROM USER_ERRORS;
BEGIN
INSERT INTO T_SCHEDULE VALUES (4001, 1001, 2002, 3002, 'Р232ХВ', 20000, TIMESTAMP '2021-10-02 12:30:00.0', TIMESTAMP '2021-10-02 17:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4002, 1002, 2003, 3003, 'А847КР', 8000, TIMESTAMP '2021-10-02 08:15:00.0', TIMESTAMP '2021-10-02 12:15:00.0');
INSERT INTO T_SCHEDULE VALUES (4003, 1003, 2004, 3005, 'С966ЕС', 5000, TIMESTAMP '2021-10-02 10:45:00.0', TIMESTAMP '2021-10-02 13:45:00.0');
INSERT INTO T_SCHEDULE VALUES (4004, 1004, 2005, 3001, 'Т138УВ', 10000, TIMESTAMP '2021-10-02 10:30:00.0', TIMESTAMP '2021-10-02 15:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4005, 1005, 2006, 3006, 'Р364ВЕ', 15000, TIMESTAMP '2021-10-02 09:00:00.0', TIMESTAMP '2021-10-02 11:00:00.0');
INSERT INTO T_SCHEDULE VALUES (4006, 1001, 2007, 3005, 'О117УУ', 7000, TIMESTAMP '2021-10-03 14:10:00.0', TIMESTAMP '2021-10-02 17:10:00.0');
INSERT INTO T_SCHEDULE VALUES (4007, 1002, 2008, 3002, 'Н439АМ', 30000, TIMESTAMP '2021-10-03 10:40:00.0', TIMESTAMP '2021-10-03 15:40:00.0');
INSERT INTO T_SCHEDULE VALUES (4008, 1003, 2009, 3003, 'О896МТ', 4000, TIMESTAMP '2021-10-02 14:30:00.0', TIMESTAMP '2021-10-02 18:30:00.0');
INSERT INTO T_SCHEDULE VALUES (4009, 1004, 2010, 3004, 'Т694КС', 12000, TIMESTAMP '2021-10-03 09:50:00.0', TIMESTAMP '2021-10-03 17:50:00.0');
INSERT INTO T_SCHEDULE VALUES (4010, 1005, 2001, 3001, 'У601КК', 9000, TIMESTAMP '2021-10-02 16:00:00.0', TIMESTAMP '2021-10-02 20:00:00.0');
END;
/
1 rows affected
INSERT INTO T_SCHEDULE VALUES (4011, 1005, 2001, 3001, 'У601КК', 9000, TIMESTAMP '2021-10-02 16:00:00.0', TIMESTAMP '2021-10-02 20:00:00.0');
1 rows affected
INSERT INTO T_SCHEDULE VALUES (4012, 1005, 2001, 3001, 'У601КК', 9000, TIMESTAMP '2021-10-02 16:00:00.0', TIMESTAMP '2021-10-02 20:00:00.0');
ORA-20000: Cannot schedule for more than 8 hours in a day.
ORA-06512: at "FIDDLE_GCXGQRQULUSCYACMVTAK.HOURS_A_DAY", line 12
ORA-04088: error during execution of trigger 'FIDDLE_GCXGQRQULUSCYACMVTAK.HOURS_A_DAY'