If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: mutating trigger issue...need help

When a record is updated in kb_errortransaction I want it inserted in to kb_stagetransaction. In the insert I also update\null 3 fields. After this update I want to delete the record from kb_errortransaction but...the delete (at the bottom) is causing problems...mutating table error.

I'm new to Oracle and triggers so explain this as if I know nothing!

CREATE OR REPLACE TRIGGER ERROR_CORRECTION
after update on kb_errortransaction
REFERENCING NEW AS newRow
FOR EACH ROW
begin
/*INSERT INTO KB_STAGEKEY
SELECT STAGEKEY FROM KB_ERRORTRANSACTION; */

Originally posted by satish_ct
If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

I don't agree with that. There are other ways to work around the mutating table issue, without sacraficing declarative referential constraints.

The problem in this case is that the trigger wants to delete the very record that was updated to fire the trigger:

CREATE OR REPLACE TRIGGER ERROR_CORRECTION
after update on kb_errortransaction
REFERENCING NEW AS newRow
FOR EACH ROW
begin
...delete kb_errortransaction where stagekey= :newrow.stagekey;
end;

The standard work around is to defer the problem code until an AFTER trigger at statement level (i.e. not FOR EACH ROW), using a packaged collection to store details of the records to be deleted:

Code:

create or replace package pkg is
type stagekey_tab_type is table of kb_errortransaction.stagekey%type index by binary_integer;
stagekey_tab stagekey_tab_type;
end;
/
CREATE OR REPLACE TRIGGER ERROR_CORRECTION
after update on kb_errortransaction
REFERENCING NEW AS newRow
FOR EACH ROW
begin
-- Do everything except the problem delete
...
-- Do this instead of the delete
pkg.stagekey_tab( stagekey_tab.count+1 ) := :newrow.stagekey;
end;
/
CREATE OR REPLACE TRIGGER ERROR_CORRECTION2
after update on kb_errortransaction
REFERENCING NEW AS newRow
-- Note: no FOR EACH ROW here!
begin
-- Delete all the rows
forall i in 1..pkg.stagekey_tab.count
delete kb_errortransaction where stagekey= pkg.stagekey_tab(i);
-- Clear the table for next time
pkg.stagekey_tab.delete;
end;
/

BTW, there is no need to use that REFERENCING clause. You can just refer to :new.stagekey etc. in the trigger.