ORA-04091, triggers and Mutation tables

[hpaille]

I have written a trigger AFTER Update that add the time of the modification and the user id to the modified record to ensure a traceability.

When the after update is triggered I have the error message "ORA-04091" that says that I can't access a mutation table, i.e. as the table is already in modification, thetrigger can't add more information.

Is there a way to achieve this via the trigger ?

Thanks in advance

 

[mahesh1234]

Hi,

U try to create before update trigger like this

create trigger bef_upd before update on <table_name>
for each row
begin
:new.db_user := user;
:new.updated_time := SYSDATE;
end;

hope this helps

mahesh

 

[sem]

This is normally resolved by postponding real changes untill post-update statement level trigger. You may store row changes somewhere temporary (in temporary table or package variable) from row-level trigger and then loop through these changes and apply them from statement-level trigger. Don't forget to set some flag to avoid recursion.

Regards, Dima

http://seminihin.narod.ru