-
create or replace trigger acc_det_update_trigger before update on
account_details for each row
begin
insert into acc_det_backup select * from acc_det where Comp_acct in
(select comp_acct from acc_det where comp_acct =:old.comp_acct );
end ;
/
Hear I want to insert the rows into the backup table before the rows are updated .I am getting these errors
ERROR at line 1:
ORA-04091: table TESTUSER.ACC_DET is mutating, trigger/function may not see it
ORA-06512: at "TESTUSER.ACC_DET_UPDATE_TRIGGER", line 2
ORA-04088: error during execution of trigger 'TESTUSER.ACC_DET_UPDATE_TRIGGER'
-
You can't look at or modify a table (acc_det in this case) that is mutating (changing) in a trigger. You will need to rewrite the query so it avoids doing a double select on acc_det.
-
Hello;
You are not allow to see the tables data in which trigger you are currently. This is for row-level-trigger;
but you can store data in a data of a package ( when you are in row-level-trigger ) and read that data from package on statement-trigger-level;
Orca
-
Create the trigger as an atonomus transaction and from it call a function that run your select and return the columns/values you need.
-
This trigger fires before update on the account_details table but at runtime reports the acc_det table as mutating.
It looks like it's fired by an update statement inside a trigger on the acc_det table.
If this is the case you should make the backup of the acc_det row in a before update trigger on the acc_det table, not on the account_details table.
Then the trigger could look like this:
Code:
create or replace trigger acc_det_update_trigger before update on
acc_det for each row
begin
insert into acc_det_backup (comp_acct, ... list of other columns here ...)
values (:old.comp_acct, ... other :old. columns here ...);
end ;
/
If you made a typo and the acc_det and account_details tables are the same, you can use the trigger above as well.
Ales
