Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Trigger
Mutating Trigger [message #334376] Wed, 16 July 2008 05:57 Go to next message
Anita123
Messages: 1
Registered: July 2008
Junior Member
Hi,

I am facing an issue with mutating table. To resolve it i followed the following approach.
http://asktom.oracle.com/tkyte/Mutate/index.html

There is still an issue that the earlier values are getting retianed in the array. this is because the triggers are not getting called in a specific pattern , but are getting invoked randonly.
Follwoing is my code:

create or replace package cig_taxonomy_state_upd_pkg
as
type myArray is table of taxonomy%rowtype
index by binary_integer;

empty myArray;
oldvals myArray;
end;


create or replace trigger taxonomy_trg_e
before update on taxonomy
begin
cig_taxonomy_state_upd_pkg.oldvals := cig_taxonomy_state_upd_pkg.empty;
dbms_output.put_line('t E');
end;

create or replace trigger taxonomy_trg_f
before update on taxonomy
for each row
declare
i number default cig_taxonomy_state_upd_pkg.oldvals.count+1;
begin
cig_taxonomy_state_upd_pkg.oldvals(i).id := :old.id;
cig_taxonomy_state_upd_pkg.oldvals(i).level1 := :old.level1;
cig_taxonomy_state_upd_pkg.oldvals(i).level2 := :old.level2;
cig_taxonomy_state_upd_pkg.oldvals(i).level3 := :old.level3;
dbms_output.put_line('t F');
end;

create or replace trigger taxonomy_trg_g
before update on taxonomy
declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_count number:=0;
level1_error Exception;
level2_error Exception;
begin
dbms_output.put_line('count=='||cig_taxonomy_state_upd_pkg.oldvals.count);
for i in 1 .. cig_taxonomy_state_upd_pkg.oldvals.count loop
if(cig_taxonomy_state_upd_pkg.oldvals(i).level1 is not null and cig_taxonomy_state_upd_pkg.oldvals(i).level2 is null) then
SELECT COUNT(1) INTO v_count FROM TAXONOMY WHERE level1= cig_taxonomy_state_upd_pkg.oldvals(i).level1;
end if;
if(v_count >1)then
raise level1_error;
end if;
v_count:= 0;
if(cig_taxonomy_state_upd_pkg.oldvals(i).level1 is not null and cig_taxonomy_state_upd_pkg.oldvals(i).level2 is not null and cig_taxonomy_state_upd_pkg.oldvals(i).level3 is null) then
SELECT COUNT(1) INTO v_count FROM TAXONOMY WHERE level2= cig_taxonomy_state_upd_pkg.oldvals(i).level2 ;
end if;
if(v_count >1)then
raise level2_error;
end if;
end loop;
EXCEPTION
WHEN level1_error THEN
RAISE_APPLICATION_ERROR(-20001, 'CAN NOT UPDATE THIS ROW AS IT HAS REFERENCING LEVEL1');
WHEN level2_error THEN
RAISE_APPLICATION_ERROR(-20002, 'CAN NOT UPDATE THIS ROW AS IT HAS REFERENCING LEVEL2');
end;

Can anyone help in resolve this issue.

Thanks,
Anita
Re: Mutating Trigger [message #334378 is a reply to message #334376] Wed, 16 July 2008 05:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read the OraFAQ Forum Guide before posting.
Re: Mutating Trigger [message #334380 is a reply to message #334376] Wed, 16 July 2008 06:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member

1) Trigger TAXONOMY_TRG_G should be an AFTER UPDATE, rather than a BEFORE UPDATE trigger.
2) Trigger TAXONOMY_TRG_F should be an AFTER UPDATE FOR EACH ROW, rather than a BEFORE UPDATE FOR EACH ROW.

This should guarantee the firing order as E,F,G
Previous Topic: ORA-01445: cannot select ROWID from a join view without a key-preserved table
Next Topic: parameters of sub functions
Goto Forum:
  


Current Time: Fri Dec 09 23:01:10 CST 2016

Total time taken to generate the page: 0.15182 seconds