CODEWITHSUNDEEP
sqloracle-databasetriggers
kkung
kkung

Reputation: 743

A trigger that inserts several rows instead of one

We have an issue with the following trigger. We would like to insert a row into the UPDATEPROCESSINFO table when there is no row with the new INSTANCEID and update it for the next ones.

But we were surprised to discover that sometimes we have multiple rows with the same INSTANCEID. Is it because it was very fast? How to prevent this from happening? Our aim is to have one row per INSTANCEID.

Thanks for help

create or replace TRIGGER TRIG_UPDATE_PROCESS_INFO
  AFTER INSERT ON PROCESSSTEP
  FOR EACH ROW
DECLARE
  AUDIT_TIME TIMESTAMP(6);
BEGIN
  SELECT MAX(LASTUPDATETIME)
    INTO AUDIT_TIME
    FROM UPDATEPROCESSINFO
    WHERE INSTANCEID = :NEW.INSTANCEID;

  IF AUDIT_TIME IS NULL THEN
    INSERT INTO UPDATEPROCESSINFO
      (INSTANCEID, STEPID, STEPSTATUS, STEPITERATION, LASTUPDATETIME)
    VALUES
      (:NEW.INSTANCEID, :NEW.STEPID, :NEW.STATUS, :NEW.STEPITERATION, :NEW.AUDITTIMESTAMP);
  ELSIF :NEW.AUDITTIMESTAMP > AUDIT_TIME THEN
    UPDATE UPDATEPROCESSINFO
      SET STEPID = :NEW.STEPID,
          LASTUPDATETIME = :NEW.AUDITTIMESTAMP,
          STEPSTATUS = :NEW.STATUS,
          STEPITERATION = :NEW.STEPITERATION
      WHERE INSTANCEID = :NEW.INSTANCEID;
  END IF;
END;

Upvotes: 0

Views: 78

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

This may be occurring because you have multiple sessions which are inserting into PROCESSSTEP for the same INSTANCEID. If two of the sessions insert into PROCESSSTEP at nearly the same time, and neither of them has committed their changes, then neither session will "see" the other's changes, and both will think that a row does not exist in UPDATEPROCESSINFO.

In my view this design appears to have a problem. I suggest changing it to have a PROCESS_STEP_HISTORY table, and as each step in the process is completed a row is inserted into PROCESS_STEP_HISTORY to record the information for the process step that was completed. Then, when something needed to find out information about the "last" step which was completed it would just do something like

SELECT a.*
  FROM (SELECT *
          FROM PROCESS_STEP_HISTORY h
          WHERE INSTANCE_ID = whatever
          ORDER BY LASTUPDATETIME DESC) a
  WHERE ROWNUM = 1

It also has the advantage of preserving information about every step in the process, which may prove useful.

I also don't recommend using a trigger to do this sort of thing. This is business logic, and putting business logic into triggers is never a good idea.

Best of luck.

Upvotes: 1

Related Questions