Creating a trigger to automatically calculate the value of total tour time-CodePudding

I am facing some issue while creating a trigger that can automatically calculate the total time it takes for a tour to finish, below is the text question

The event holder has decided to record the shortest tour time for each event type for all future exhibitions. Add two new attributes in the EVENTTYPE table named eventype_record and eventtype_recordholder to store the shortest tour time for each event type and the participant (participant number) who holds the record. You may assume that only a single participant will hold each record and that the record holder will only be replaced by a new single participant if their tour time is less than the current eventtype_record. Calculate the tour time attribute added in previous task when the tour finish time is updated (ie. the participant finished the tour)

What I currently have is

CREATE OR REPLACE TRIGGER tour_time_updater 
  AFTER UPDATE OF tour_finishtime ON entry 
  FOR EACH ROW 
BEGIN

    UPDATE entry 
    SET tour_time = to_char(((:new.tour_finishtime - :old.tour_starttime) * 24 * 60), '9999.99');
END;
/

But after I try to insert a fake participant in my entry table with no finish time with this code

INSERT INTO entry (
    event_id,
    entry_no, 
    tour_starttime, 
    tour_finishtime,
    part_no,
    group_id,
    tour_time 

) VALUES ( 
    9,
    6,
    to_date('09:05:43', 'HH:MI:SS'),
    NULL,
    5,
    NULL, 
    NULL
);

and only update its tour_finishtime later

UPDATE entry SET tour_finishtime = to_date('10:05:43', 'HH:MI:SS')
where part_no = 5 and event_id = 9;

it is giving me errors like

**UPDATE entry SET tour_finishtime = to_date('10:05:43', 'HH:MI:SS')
where part_no = 5 and event_id = 9
Error report -
ORA-04091: table XXXXXXXX.ENTRY is mutating, trigger/function may not see it
ORA-06512: at "XXXXXXXX.TOUR_TIME_UPDATER", line 5
ORA-04088: error during execution of trigger 'XXXXXXXX.TOUR_TIME_UPDATER'**

Can anyone help me with that? Thank you in advance!

CodePudding user response：

Don't update the table (literally), but

CREATE OR REPLACE TRIGGER tour_time_updater 
  BEFORE UPDATE OF tour_finishtime ON entry 
  FOR EACH ROW 
BEGIN
  :new.tour_time := to_char(((:new.tour_finishtime - :new.tour_starttime) * 24 * 60), '9999.99');
END;
/

Code you wrote is trying to update the same table whose modification fired the trigger; for the trigger, that table is "mutating" and that's an invalid condition.

You could "fix" it by writing a compound trigger (if your database version supports it) or using a package, but - why bother, if correct way to do it is as suggested in code above?

CodePudding user response：

Don't use a TRIGGER, use a virtual column:

CREATE TABLE entry (
    event_id        NUMBER PRIMARY KEY,
    entry_no        NUMBER, 
    tour_starttime  DATE, 
    tour_finishtime DATE,
    part_no         NUMBER,
    group_id        NUMBER
);

ALTER TABLE entry ADD tour_time INTERVAL DAY(0) TO SECOND (0)
  GENERATED ALWAYS AS ((tour_finishtime - tour_starttime) DAY(0) TO SECOND(0));

ALTER TABLE entry ADD tour_time NUMBER(7,2)
  GENERATED ALWAYS AS ((tour_finishtime - tour_starttime) *24 * 60);

If you must use a trigger (don't) then:

CREATE TABLE entry (
    event_id        NUMBER PRIMARY KEY,
    entry_no        NUMBER, 
    tour_starttime  DATE, 
    tour_finishtime DATE,
    part_no         NUMBER,
    group_id        NUMBER,
    tour_time       INTERVAL DAY(0) TO SECOND(0)
);

CREATE OR REPLACE TRIGGER tour_time_updater 
  BEFORE INSERT OR UPDATE OF tour_finishtime ON entry 
  FOR EACH ROW 
BEGIN
  :NEW.tour_time := (:new.tour_finishtime - :new.tour_starttime) DAY(0) TO SECOND(0);
END;
/

db<>fiddle here