After-update trigger [message #297164] |
Wed, 30 January 2008 07:20  |
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 #297179 is a reply to message #297164] |
Wed, 30 January 2008 08:32   |
Frank
Messages: 7901 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 #297183 is a reply to message #297181] |
Wed, 30 January 2008 08:43   |
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  |
 |
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
|
|
|