By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,971 Members | 1,732 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,971 IT Pros & Developers. It's quick & easy.

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

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER moto_pvs_pulse_daily_yield_trg after INSERT ON moto_pvs_pulse_daily_yield_tmp FOR EACH ROW
  2. DECLARE
  3.      v_sql_error        moto_pvs_db_error.ora_error%TYPE;
  4.  
  5. BEGIN
  6.         IF :NEW.board_yield >= 0 THEN
  7.             insert into mt_pulse_daily_yield_tmp --MotoTrak Table
  8.             (factory_id,line_id,board_item_number,manufactory_day,panel_yield,board_yield,insert_datetime)
  9. values
  10. (:NEW.factory_id,:NEW.line_id,:NEW.board_item_number,:NEW.manufactory_day,:NEW.panel_yield,:NEW.board_yield,:NEW.insert_datetime);
  11.  
  12. DELETE MOTO_PVS_PULSE_DAILY_YIELD_TMP;
  13.  
  14. ELSE
  15.         INSERT INTO MOTO_PVS_DB_ERROR(FROM_PROCEDURE,ERROR_DATE,MESSAGE)
  16. VALUES
  17. ( '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');
  18.  
  19. END IF;   
  20.  
  21. EXCEPTION
  22.   WHEN OTHERS THEN
  23.     v_sql_error := SQLERRM;
  24.     INSERT INTO moto_pvs_db_error (from_procedure, error_date, message, ora_error)
  25.     VALUES ('MOTO_PVS_PULSE_DAILY_YIELD_TRG', SYSDATE, 'ERROR DURING UPLOADING YIELD DATA', v_sql_error);
  26.  
  27. 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.
Sep 3 '07 #1
Share this Question
Share on Google+
4 Replies


amitpatel66
Expert 100+
P: 2,367
This Error is caused because the TRIGGER is trying to perform DML operation on the table on which it is based upon.
Sep 3 '07 #2

amitpatel66
Expert 100+
P: 2,367
Check out for solution Here
Sep 3 '07 #3

P: 2
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
Sep 4 '07 #4

amitpatel66
Expert 100+
P: 2,367
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.
Sep 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.