By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T_MASTER
(MASTER_ID NUMBER PRIMARY KEY);
INSERT INTO T_MASTER
SELECT 1001 FROM DUAL UNION ALL
SELECT 1002 FROM DUAL;
2 rows affected
CREATE TABLE T_CLIENT
(CLIENT_ID NUMBER PRIMARY KEY);
INSERT INTO T_CLIENT
SELECT 2002 FROM DUAL UNION ALL
SELECT 2003 FROM DUAL;
2 rows affected
CREATE TABLE T_BOX
(BOX_NUM NUMBER PRIMARY KEY);
INSERT INTO T_BOX
SELECT 3002 FROM DUAL UNION ALL
SELECT 3003 FROM DUAL;
2 rows affected
CREATE TABLE T_CAR
(CAR_NUM VARCHAR2(10) PRIMARY KEY);
INSERT INTO T_CAR
SELECT 'Р232ХВ' FROM DUAL UNION ALL
SELECT 'А847КР' FROM DUAL;
2 rows affected
CREATE TABLE T_SCHEDULE
(
sched_id number(4) NOT NULL,
master_id number(10) CONSTRAINT T_SCHEDULE_FK1 REFERENCES T_MASTER(master_id) ON DELETE CASCADE NOT NULL,
client_id number(10) CONSTRAINT T_SCHEDULE_FK2 REFERENCES T_CLIENT(client_id) ON DELETE CASCADE NOT NULL,
box_num number(5) CONSTRAINT T_SCHEDULE_FK3 REFERENCES T_BOX(box_num) ON DELETE CASCADE NOT NULL,
car_num varchar2(10) CONSTRAINT T_SCHEDULE_FK4 REFERENCES T_CAR(car_num) ON DELETE CASCADE NOT NULL,
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
DECLARE
v_count number := 0;
BEGIN
WITH cteInterval
AS (SELECT JOB_START,
MASTER_ID,
job_stop - job_start AS JOB_INTERVAL
FROM T_SCHEDULE)
SELECT COUNT(*)
INTO v_count
FROM (SELECT SUM((EXTRACT(DAY FROM JOB_INTERVAL) * 24 * 60 * 60) +
(EXTRACT(HOUR FROM JOB_INTERVAL) * 60 * 60) +
(EXTRACT(MINUTE FROM JOB_INTERVAL) * 60) +
(EXTRACT(SECOND FROM JOB_INTERVAL))) / 60 AS daily
FROM cteInterval
GROUP BY TRUNC(job_start, 'DD'),
master_id)
WHERE daily > 480;
IF v_count <> 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Difference greater than 8 hours found');
END IF;
END HOURS_A_DAY;
/
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');
1 rows affected
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');
1 rows affected
WITH cteInterval
AS (SELECT JOB_START,
JOB_STOP,
MASTER_ID,
job_stop - job_start AS JOB_INTERVAL
FROM T_SCHEDULE)
SELECT MASTER_ID,
JOB_START,
JOB_START,
JOB_INTERVAL,
((EXTRACT(DAY FROM JOB_INTERVAL) * 24 * 60 * 60) +
(EXTRACT(HOUR FROM JOB_INTERVAL) * 60 * 60) +
(EXTRACT(MINUTE FROM JOB_INTERVAL) * 60) +
EXTRACT(SECOND FROM JOB_INTERVAL)) / 60 AS daily
FROM cteInterval
| MASTER_ID | JOB_START | JOB_START | JOB_INTERVAL | DAILY |
|---|---|---|---|---|
| 1001 | 02-OCT-21 12.30.00.000000 | 02-OCT-21 12.30.00.000000 | +000000000 05:00:00.000000 | 300 |
| 1002 | 02-OCT-21 08.15.00.000000 | 02-OCT-21 08.15.00.000000 | +000000000 04:00:00.000000 | 240 |