Oracle Trigger to update a the same table

Question

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!

vkamayiannis · Accepted Answer · 2012-11-21 11:56:20Z

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;

edited Nov 21, 2012 at 11:56

answered Nov 21, 2012 at 11:17

vkamayiannis

7353 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wkdshot Over a year ago

Thanks for your reply. Your code gives this error ORA-04084: cannot change NEW values for this trigger type

vkamayiannis Over a year ago

my mistake. a before insert or update trigger would do the job

Ajith Sasidharan · Accepted Answer · 2012-11-21 11:56:19Z

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;

edited Nov 21, 2012 at 11:56

answered Nov 21, 2012 at 11:17

Ajith Sasidharan

1,1537 silver badges7 bronze badges

2 Comments

wkdshot Over a year ago

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?

Ajith Sasidharan Over a year ago

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;

Collectives™ on Stack Overflow

Oracle Trigger to update a the same table

2 Answers 2

2 Comments

here is the sample code

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

here is the sample code

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related