Help: Trigger -Not able to delete data from trigger table.

[Alexis]

Hello,

I'm facing oracle trigger problem. Anyone can help or advise how to resolve it?

Below are the explaination on my problem

I've created a trigger for my program. When there is a new data insert into table moto_pvs_pulse_daily_yield_tmp, my program will checking and copy the data by each row and insert into table mt_pulse_daily_yield_tmp which is in other instance.

After I success insert into table mt_pulse_daily_yield_tmp, I need to delete the data in the original table (moto_pvs_pulse_daily_yield_tmp), but I'm getting the error as below:

ORA-04091: table MOTOPVS_OWN.MOTO_PVS_PULSE_DAILY_YIELD_TMP is mutating, trigger/function may not see it


Here are my code:

CREATE OR REPLACE TRIGGER moto_pvs_pulse_daily_yield_trg after INSERT ON moto_pvs_pulse_daily_yield_tmp FOR EACH ROW
DECLARE
     v_sql_error        moto_pvs_db_error.ora_error%TYPE;
 
BEGIN
        IF :NEW.board_yield >= 0 THEN
            insert into mt_pulse_daily_yield_tmp --MotoTrak Table
            (factory_id,line_id,board_item_number,manufactory_day,panel_yield,board_yield,insert_datetime)
values
(:NEW.factory_id,:NEW.line_id,:NEW.board_item_number,:NEW.manufactory_day,:NEW.panel_yield,:NEW.board_yield,:NEW.insert_datetime);
 
DELETE MOTO_PVS_PULSE_DAILY_YIELD_TMP;
 
ELSE
        INSERT INTO MOTO_PVS_DB_ERROR(FROM_PROCEDURE,ERROR_DATE,MESSAGE)
VALUES
( 'MOTO_PVS_PULSE_DAILY_YIELD_TRG', SYSDATE , 'BOARD YIELD VALUE FOR FACTORY_ID: ('||:NEW.factory_id||') AND LINE_ID: ('||:NEW.line_id||') MUST BE POSITIVE');
 
END IF;   
 
EXCEPTION
  WHEN OTHERS THEN
    v_sql_error := SQLERRM;
    INSERT INTO moto_pvs_db_error (from_procedure, error_date, message, ora_error)
    VALUES ('MOTO_PVS_PULSE_DAILY_YIELD_TRG', SYSDATE, 'ERROR DURING UPLOADING YIELD DATA', v_sql_error);
 
END moto_pvs_pulse_daily_yield_trg;

/


P/S: I believed this line is "DELETE MOTO_PVS_PULSE_DAILY_YIELD_TMP;" is caused the problem. but how to resolve it? Is really highly appreciate if anyone can advise me. Thank you very much.

[amitpatel66]

This Error is caused because the TRIGGER is trying to perform DML operation on the table on which it is based upon.

[amitpatel66]

Check out for solution Here

[Alexis]

Hi,

Thanks for your quick response. I had read through the solution url you provide, but still not really understand how to resolve the problem. Can I delete the data for it own table in the trigger itself?

Thanks

[amitpatel66]

Follow the below steps:

1. Create a Package Spec that will have two Arrays (one will hold the ROWID of the newly inserted value and the second one is to reset the first one else u can also use empty constructor)

2. Create a BEFORE INSERT statement LEVEL TRIGGER ON MOTOPVS_OWN.MOTO_PVS_PULSE_DAILY_YIELD_TMP to bring the Package to its consistent state and initialisze the NULL Varray.

3. Create a After INSERT ROWLEVEL on MOTOPVS_OWN.MOTO_PVS_PULSE_DAILY_YIELD_TMP to get the ROWID of the newly inserted row.

4. Create a AFTER INSERT statement level on MOTOPVS_OWN.MOTO_PVS_PULSE_DAILY_YIELD_TMP and use the ROWID in the package VARRAY to insert that row in to mt_pulse_daily_yield_tmp table.
Then delete the Row in MOTOPVS_OWN.MOTO_PVS_PULSE_DAILY_YIELD_TMP after it is inserted in to mt_pulse_daily_yield_tmp table.

This should work. Let me know if it does not.