So, I am creating a database using Oracle and this is the problem I ran into. I have this table:

CREATE TABLE T_SCHEDULE
(
    sched_id number(4) NOT NULL,
    master_id number(10) REFERENCES T_MASTER(master_id) ON DELETE CASCADE NOT NULL,
    client_id number(10) REFERENCES T_CLIENT(client_id) ON DELETE CASCADE NOT NULL,
    box_num number(5) REFERENCES T_BOX(box_num) ON DELETE CASCADE NOT NULL,
    car_num varchar2(10) 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)
);

And this trigger:

CREATE OR REPLACE TRIGGER HOURS_A_DAY
    BEFORE INSERT OR UPDATE ON T_SCHEDULE
    FOR EACH ROW
    DECLARE
    v_count number := 0;
    BEGIN
        SELECT COUNT(*)
        INTO v_count
        FROM (SELECT SUM(TO_NUMBER(TRUNC(TO_DATE(job_stop - job_start), 'MI'))) daily
            FROM T_SCHEDULE
            GROUP BY TRUNC(job_start, 'DD'), master_id)
        WHERE daily > 480;
        IF v_count <> 0 THEN
            ROLLBACK;
        END IF;
    END;

(The table has references to other tables, but I don't think it's relevant to the problem). This are the values I am inserting:

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');

When I run this, the first row is inserted, however all the others receive ORA-01847 (day of month must be between 1 and last day of month), ORA-06512 (at "SYSTEM.HOURS_A_DAY", line 4) and ORA-04088 (error during execution of trigger 'SYSTEM.HOURS_A_DAY') errors. The main one I think is ORA-01847. All of this to me is weird, since I inserted the same values in the same table, just without the trigger and everything was fine. So I am guessing my trigger affects it. How can I fix it?

CodePudding user response：

The problem is in SUM(TO_NUMBER(TRUNC(TO_DATE(job_stop - job_start), 'MI'))). job_stop - job_start, a calculation involving two TIMESTAMPs, returns a INTERVAL rather than a DATE or a NUMBER. As such, you need to extract the days, hours, minutes, and seconds from the resulting interval, adjusting each to a value in seconds.

You've also got a couple other potential issues:

You're selecting from T_SCHEDULE in a trigger defined on T_SCHEDULE. This is likely to cause an ORA-04091 "Table T_SCHEDULE is mutating; trigger cannot see it" error. The solution is to define this as a statement trigger rather than a row trigger, by simply removing the FOR EACH ROW line. In this case this should work, but in many cases it's not a viable solution and other solutions will have to be tried.
You're trying to perform a ROLLBACK in a trigger. This is not allowed - raise an exception instead.

Putting this all together we get:

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;

db<>fiddle here

CodePudding user response：

You appear to be checking, for each master_id and day, if the total of the time difference between the job start and job end time is more than 8 hours (before you insert the new row).

Which could be simplified to:

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;
/

db<>fiddle here

Update - Including the new rows:

Create the types:

CREATE TYPE hours_detail IS OBJECT(
  master_id NUMBER(10),
  day       DATE,
  hours     NUMBER
);

CREATE TYPE hours_tbl IS TABLE OF hours_detail;

Then the compound trigger:

CREATE TRIGGER hours_a_day
FOR INSERT OR UPDATE ON t_schedule
COMPOUND TRIGGER
  hours        hours_tbl;
  v_overbooked PLS_INTEGER;
BEFORE STATEMENT
  IS
  BEGIN
    SELECT hours_detail(
             master_id,
             TRUNC(job_start),
             SUM(CAST(job_stop AS DATE) - CAST(job_start AS DATE)) * 24
           )
    BULK COLLECT INTO hours
    FROM   T_SCHEDULE
    GROUP BY master_id, TRUNC(job_start);
  END BEFORE STATEMENT;
BEFORE EACH ROW
  IS
  BEGIN
    hours.EXTEND();
    hours(hours.COUNT) := hours_detail(
      :NEW.master_id,
      TRUNC(:NEW.job_start),
      (CAST(:NEW.job_stop AS DATE) - CAST(:NEW.job_start AS DATE)) * 24
    );
  END BEFORE EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    SELECT 1
    INTO   v_overbooked
    FROM   TABLE(hours)
    GROUP BY master_id, day
    HAVING SUM(hours) > 8
    FETCH FIRST ROW ONLY;

    RAISE_APPLICATION_ERROR(
      -20000,
      'Cannot schedule for more than 8 hours in a day.'
    );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END AFTER STATEMENT;
END;
/

This will raise errors when multiple rows are inserted at the same time that individually do not total more than 8 hours but together go over that limit.

db<>fiddle here