Home » SQL & PL/SQL » SQL & PL/SQL » mutating trigger function (10g windows)
mutating trigger function [message #308328] Mon, 24 March 2008 01:08 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i have procedure which will insert data to bs_table ,need to update the records in bs_table ,table when data inserted into table i need to update with the sales_pnt_id for each record inserted

CREATE OR REPLACE TRIGGER logbstp2
AFTER insert ON bs_table for each row
declare
PRAGMA autonomous_transaction; 
i_spid 	sales_pnt.pk_sales_pnt_id%type;
spid number;
v_count number;
i_tran_date		date;
v_inputted_date    date;
cursor c1 is select pk_sales_pnt_id  from sales_pnt;
BEGIN
open c1;
fetch c1 into spid ;
v_inputted_date := i_tran_date;
select count(1) into v_count from bs_table where inputted_date = v_inputted_date;
   IF v_count = 0  THEN
    update 	bs_table
	set 	volt_tp_id = 2,
			inputted_date = v_inputted_date,
			sales_pnt_id = spid
	where 	inputted_date IS NULL;
	commit;
	end if;
END;
/



ERROR at line 1:
ORA-04091: table bs_table is mutating, trigger/function
may not see it
ORA-06512: at "LOGBSTP2", line 12
ORA-04088: error during execution of trigger 'LOGBSTP2'
ORA-06512: at "BS_PKG", line 249
ORA-06512: at line 1
Re: mutating trigger function [message #308331 is a reply to message #308328] Mon, 24 March 2008 01:11 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
Oracle precludes accessing the the table upon which a trigger has fired by the trigger code itself.

[Updated on: Mon, 24 March 2008 01:12] by Moderator

Report message to a moderator

Re: mutating trigger function [message #308342 is a reply to message #308328] Mon, 24 March 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use MERGE instead of INSERT.

Regards
Michel
Re: mutating trigger function [message #308366 is a reply to message #308342] Mon, 24 March 2008 02:35 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
merge in trigger ???
Re: mutating trigger function [message #308373 is a reply to message #308366] Mon, 24 March 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
merge WITHOUT trigger, merge INSTEAD OF insert.

Regards
Michel
Re: mutating trigger function [message #308416 is a reply to message #308373] Mon, 24 March 2008 04:43 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
after each insert i need to update the bs.sales_pnt_id

MERGE INTO  bILL BS
   USING (select s.pk_sales_pnt_id FROM sales_pnt s, bill b
           where b.sales_pnt_id = pk_sales_pnt_id
		   and b.volt_tp_id is null
		     )sp
      ON (bs.sales_pnt_id <> Sp.pk_sales_pnt_id)
   WHEN MATCHED THEN update 	set 
	 	bs.volt_tp_id = 2,
			bs.inputted_date=v_inputted_date,
			bs.sales_pnt_id = pk_sales_pnt_id
	where 	bs.volt_tp_id is null;



ERROR at line 1:
ORA-38104: Columns referenced in the ON Clause cannot be updated:
"BS"."SALES_PNT_ID"
ORA-06512: at "sTest.MBS_PKG", line 884
ORA-06512: at line 1
Re: mutating trigger function [message #308424 is a reply to message #308416] Mon, 24 March 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain the real, business need not the need you created with the way you want to implement the business need.

Regards
Michel
Re: mutating trigger function [message #308555 is a reply to message #308424] Mon, 24 March 2008 17:43 Go to previous message
usmanqamar
Messages: 6
Registered: January 2007
Junior Member
Hi,
I think Michel has done you everything but i can let you know if you dont have any idean abt mutating trigger.A mutating trigger occurs when the table that originally fired the triggering event is being accessed in the body of the trigger code.
Regards
Usman

[Updated on: Tue, 25 March 2008 00:58] by Moderator

Report message to a moderator

Previous Topic: ORA error to PL/SQL exception
Next Topic: Break the Lines from a table and insert into new table
Goto Forum:
  


Current Time: Fri Dec 02 12:21:42 CST 2016

Total time taken to generate the page: 0.05803 seconds