proc doesn't work when called from trigger

[cadoltt]

Hello everybody,

I am not experienced in writing table triggers and I'm stuck with something I don't really understand.

I have a table which keeps track of treatments patients receive. It has a composite primary key pt_id + treatment_number. The treatments should be numbered sequentially depending on the dates they take place. However they don't necessarily come for data entry in their natural order that's why I need to re-number them at insert stage.

Here is the trigger:

CREATE OR REPLACE TRIGGER trg_trt
  AFTER INSERT 
  ON tbl_treatment
  FOR EACH ROW
DECLARE
 i integer;
BEGIN
  i := PKG_TRG_PRC.i_reorder_trt_no(:NEW.pt_id);
  dbms_output.put_line('i_err = ' || i);
END trg_trt;

And here is the function which is supposed to to the job:

  FUNCTION i_reorder_trt_no(in_pt_id IN INTEGER)
  RETURN INTEGER AS
    i_row_no                INTEGER;
    i_position              INTEGER;
    
    CURSOR crs_trt IS SELECT treatment_number,                                                     DT_CRT_DATE_FROMPARTS(year,mon,day) AS dt_trt
                       FROM tbl_Treatment
                       WHERE pt_id = in_pt_id
                       ORDER BY dt_trt
                       FOR UPDATE OF treatment_number;
                       
    row_crs_trt crs_trt%ROWTYPE;
    
  BEGIN
  
    i_position := 1;

    --first step - assign large numbers beyond possible limit 
    --to avoid PK violation
    OPEN crs_trt;
    
      LOOP
        FETCH crs_trt INTO row_crs_trt; -- retrieve a row from table
        EXIT WHEN crs_trt%NOTFOUND;
        
        UPDATE tbl_treatment
        SET treatment_number = treatment_number + 50
        WHERE CURRENT of crs_trt;
      END LOOP;
      
    CLOSE crs_trt;


    i_position := 2;
    --Re-open cursor - second step: As records in cursor set are ordered by
    --dt_trt - fetch trt records one by one and apply 1,2,3.. sequence 
    
    OPEN crs_trt;
    
      i_row_no := 1;
      LOOP
        FETCH crs_trt INTO row_crs_trt; -- retrieve a row from table
        EXIT WHEN crs_trt%NOTFOUND;
        
        UPDATE tbl_treatment
        SET treatment_number = i_row_no
        WHERE CURRENT of crs_trt;
        
        i_row_no := i_row_no + 1;
      END LOOP;
      
    CLOSE crs_trt;

    COMMIT;
    
    RETURN 0;
    
  EXCEPTION
    WHEN others THEN
      CASE i_position
        WHEN 1 THEN 
          RETURN 1;
        WHEN 2 THEN 
          RETURN 2;
        ELSE 
          RETURN 10;
      END CASE;

      ROLLBACK;

  END i_reorder_trt_no;

The function works when called from a regular test procedure, but when I insert a record into the table and the trigger fires I get a return code 1, meaning it fails at the first opening of the cursor and as a result it doesn't renumber the records.

Any idea how to fix this?

Thank you in advance,
Alex

 

[jimirvine]

The first thing to doi is get rid of what is one of the worst pieces og coding that you can write i.e.:

EXCEPTION
    WHEN others THEN
      CASE i_position
        WHEN 1 THEN
          RETURN 1;
        WHEN 2 THEN
          RETURN 2;
        ELSE
          RETURN 10;
      END CASE;

Then re-run it and see the actual error being produced. Look up documentation for that error.

 

[SantaMufasa]

Following a quick read of your code, Cadoltt, your trigger is on the same table that you are UPDATING in the trigger. That creates a "Mutating table" error in Oracle. In a trigger, at most, you should only modify the row that the trigger is processing (using the ":new." qualifier).

I believe there needs to be a rethinking of the trigger logic. (Sorry to be the bearer of unsettling news.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[jimirvine]

All of which would have been clear if the terrible when others had either not been used, or been used correctly

 

[cadoltt]

Jimirvine, SantaMufasa,

Yeah, this really turned out to be a mutating table error - at least now I know where to look at.

Thank you very much for the tip!

Alex

 

[taupirho]

You say the treaments should be numbered sequentially depending on the date they take place. Well why not base the treament id on the date it takes place then?

Patient A receives treatment today (28-Aug-08) so you could make the treatment id 20080828, key becomes A20080828

Patient A receives treatment tomorrow (29-Aug-08) so the treatment id = 20080829, key becomes B20080829

The treament id gets set on data entry because presumably the date of treatnment is known therefore it doesn't matter the order in which the treatments arrive at data entry. There is therfore no need for triggers etc ... The only problem occurs if a patient gets multiple teratments per day. Then you would have to record say the time of treatment as well and use that to create the treatment id. Don't know if this will work in your circumstances but its worth a thought.


In order to understand recursion, you must first understand recursion.

 

[cadoltt]

Hi Taupirho,

Thank you for taking part in it. I was also thinking about a composite ID, but there two things which make it hard. First, the physicians want the treatment number to be a simple sequence reflecting treatments natural order. Second, dates might be incomplete (only the year is mandatory) that's why I calculate them on the fly (function DT_CRT_DATE_FROMPARTS) applying some rules if day or month are missing. And there are times when these missing pieces become known, so the values get updated, which in turn will require renumbering.

BTW, thank Jimirvine and SantaMufasa, I have already solved the mutating table problem with combination row and statement triggers.

...For some reason, guys, I am always sure that in Tek-Tips I cam find answers to any question