Trigger errors

[Subha]

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 =ld.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'

[stecal]

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.

[Orca777]

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

[Highlander]

Create the trigger as an atonomus transaction and from it call a function that run your select and return the columns/values you need.

[ales]

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:

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