Home » SQL & PL/SQL » SQL & PL/SQL » After-update trigger (PL/SQL)
After-update trigger [message #297164] Wed, 30 January 2008 07:20 Go to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
I want to create after-update/after-insert triggers that will update a column in the same table.

I get the following error:

ORA-04091: table ARI.CONN_BILLING_MOMENT is mutating, trigger/function may not see it
ORA-06512: at "ARI.TRG_BMT_AUR", line 12
ORA-04088: error during execution of trigger 'ARI.TRG_BMT_AUR'
-------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_bmt_aur after
update on conn_billing_moment for each row

declare
lBillingMoment number;
begin
if to_number(substr(:new.reading_moment,1,2)) > 15
then lBillingMoment := to_number(substr(:new.reading_moment,1,2)) + 1;
else lBillingMoment := to_number(substr(:new.reading_moment,1,2));
end if;
--
update conn_billing_moment
set billing_moment = lBillingMoment
where id = :new.id; -- :new.id doesn't work either
end;
/
Re: After-update trigger [message #297169 is a reply to message #297164] Wed, 30 January 2008 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-04091: table %s.%s is mutating, trigger/function may not see it
 *Cause: A trigger (or a user defined plsql function that is referenced in
         this statement) attempted to look at (or modify) a table that was
         in the middle of being modified by the statement which fired it.
 *Action: Rewrite the trigger (or function) so it does not read that table.


Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Please search a little bit BEFORE posting, this is the most common error on trigger.

Regards
Michel
Re: After-update trigger [message #297178 is a reply to message #297169] Wed, 30 January 2008 08:28 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Thanks for letting me know that I'm a newbie!
Re: After-update trigger [message #297179 is a reply to message #297164] Wed, 30 January 2008 08:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you are only using columns from the record that is being inserted or updated, there is no need for an explicit update.
If you change the timing to "before", you can just do :new.billing_moment := lBillingMoment;
instead of the update
Re: After-update trigger [message #297181 is a reply to message #297179] Wed, 30 January 2008 08:35 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Wed, 30 January 2008 15:32
Since you are only using columns from the record that is being inserted or updated, there is no need for an explicit update.
If you change the timing to "before", you can just do :new.billing_moment := lBillingMoment;
instead of the update


Only when id should happen to be the primary key (or unique key for that matter) of the table.

(chances are it is Wink )
Re: After-update trigger [message #297183 is a reply to message #297181] Wed, 30 January 2008 08:43 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Thanks, that's exactly what I did.
I have moved the 'update' to the before-insert/before-update triggers where I can refer to the ':new' values..
Re: After-update trigger [message #297185 is a reply to message #297183] Wed, 30 January 2008 09:09 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
- Is your trigger working now?

- What's the primary key of the conn_billing_moment table?

- How many rows can/will be update each time within the trigger?

The last question is somehow related to the second question
Previous Topic: Why does oracle allow to create such ambiguous synonyms??
Next Topic: ORA-01410 Invalid Rowid
Goto Forum:
  


Current Time: Sun Dec 04 14:37:38 CST 2016

Total time taken to generate the page: 0.05461 seconds