Simple Trigger to update column... HELP!

[cwong5]

I am so so desperate of help. It's pretty simple by concept, but I can't seem to get it right.
Okay
2 tables.
Table A -> id, c1, c2, c3
Table B -> id, c1, c2, c3

I want a trigger that will populate c1 from table b when I insert or update the id in table a. So far, I have this:

(what did I do wrong?)



-----

CREATE OR REPLACE TRIGGER v_gcd
after update on editorial
referencing old as pre new as post
for each row
declare "qid" char(250);
begin

select quarantine_id into "qid" from gcd where ost.int_filename = gcd.FILE_NAME;
update editorial
set editorial.quara_type = "qid" where editorial.INT_FILENAME = ost.int_filename;

end v_gcd;

/

 

[cwong5]

Oh, I am getting this error:
ORA-04091: table HEMERA.EDITORIAL is mutating, trigger/function may not see it
ORA-06512: at "HEMERA.V_GCD", line 5
ORA-04088: error during execution of trigger 'HEMERA.V_GCD'

 

[rcurva]

This is a mutating table problem, a very common one. There are several workarounds posted for this kind of problem. It happens when a trigger on a table tries to insert, update, or even select the table of whose trigger is being executed. Sometimes the inability to see the table causes standstill in the development.

Try searching technet.oracle.com for mutating table.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[cwong5]

Okay. I am transferred the field I want ot update onto another table, and yet i am still getting the same error. Can anyone give me an example code?
Thanks for everyone's help. I am going insane!

 

[jee]

I may not be clear on what you are trying to do, but I think you want a BEFORE trigger so you can update the row in editorial that is being changed:

CREATE OR REPLACE TRIGGER v_gcd
before update  on editorial
referencing old as pre new as post
for each row

begin

select quarantine_id into :post.quara_type
from   gcd 
where :post.int_filename = gcd.FILE_NAME;

end v_gcd;

/

The above assumes that int_filename is a unique column in editorial and file_name is a unique column in gcd.

 

[cwong5]

I want to thank everyone who helped me! And the least I can do is to post the final trigger for reference, in case anyone else is wondering!
THANKS!

CREATE OR REPLACE TRIGGER v_gcd
before update or insert of int_filename on editorial
for each row
declare vcount number;
begin
select count(*) into vcount from gcd where ltrim(rtrim(file_name)) = ltrim(rtrimnew.int_filename));

if vcount <> 0
then
select quarantine_id into :new.QUARA_TYPE
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));
select QUARANTINE_DATE into :new.qshortdate
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select category_name into :new.category
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select friendly_name into :new.image_desc
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select quality_id into :new.qua_value
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select vendor_name into :new.producer_
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select delivery_number into :new.contractor
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select uii into :new.leg_uii
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select friendly_name into :new.image_desc
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select file_name into :new.leg_name
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select artist_name into :new.artist
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select clipart_style into :new.style
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select rights_id into :new.rights
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));

select unquarantine_id into :new.unquara_id
from gcd
where ltrim(rtrimnew.int_filename)) = ltrim(rtrim(gcd.FILE_NAME));





end if;

end;

/