2

I have a trigger which I'm trying to use to update the same table it listens to.

However, upon submitting a create or update, I get the following errors:

ORA-04091: table [username].ADDRESSES is mutating, trigger/function may not see it ORA-06512: at "[username].ADDRESSES_T1", line 9 ORA-04088: error during execution of trigger '[username].ADDRESSES_T1'

I appreciate any help!

wkdshot
  • 256
  • 1
  • 6
  • 18

2 Answers2

2

You're updating the same table the trigger is written. You need something like this:

create or replace trigger "ADDRESSES_T1"
BEFORE
insert or update on "ADDRESSES"
for each row
begin
DECLARE
   l_lat NUMBER;
   l_lng NUMBER;
   l_postcode VARCHAR2(8) := :NEW.POSTCODE;

BEGIN
  brian.POSTCODE_TO_LAT_LNG_GM_API (l_postcode, l_lat, l_lng);
  :new.location = SDO_GEOMETRY(2001, --SDO_GTYPE
                               8307, --SDO_SRID
                               SDO_POINT_TYPE(l_lng, --X lng
                                              l_lat, --Y lat
                                              null),     --Z
                               null, --SDO_ELEM_INFO_ARRAY
                               null);
END;
vkamayiannis
  • 745
  • 3
  • 13
1

Its throwing mutating table error because your trigger is getting fired on table ADDRESSES and inside the trigger body its updating the same table "ADDRESSES".

You can avoid this by using instead of triggers (creating view for table ADDRESSES).

here is the sample code

create or replace view addresses_vw as select * from addresses;

CREATE OR REPLACE TRIGGER ADDRESSES_T1
INSTEAD OF INSERT or UPDATE
ON addresses_vw
FOR EACH ROW
BEGIN
  UPDATE addresses
  SET <statements> ;
END ADDRESSES_T1;
Ajith Sasidharan
  • 1,155
  • 7
  • 7
  • Thanks for your reply. I'm not sure I quite understand. 1) Create a view on addresses (just select *) 2) Create a trigger on the view which updates the table? – wkdshot Nov 21 '12 at 11:38
  • here is the sample code ----------------------- create or replace view addresses_vw as select * from addresses; CREATE OR REPLACE TRIGGER ADDRESSES_T1 INSTEAD OF INSERT or UPDATE ON addresses_vw FOR EACH ROW BEGIN UPDATE addresses SET ; END ADDRESSES_T1; – Ajith Sasidharan Nov 21 '12 at 11:50