By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Mutating tables

P: n/a
Hello -

I'm using Oracle 8i and am running into the infamous mutating table
error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
examples for getting around mutating table errors if you either need
access to the :old or the :new values within a trigger. But what if
you need access to both? Or is this even possible (I'm fairly new to
triggers).

I have to execute a select count(*) from trigger_table where
value1=:old.value1 and value2=:old.value2. Based on this result, log
files must be written utilizing the :new values. A simplified example
of my trigger is below:

CREATE OR REPLACE TRIGGER mut_trigger
AFTER INSERT OR UPDATE OF value1, value2
ON table_a
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
numofdocs number;
BEGIN
select count(*) into numofdocs from table_a where value1=:old.value1
and value2=:old.value2;
if (numofdocs > 0) then
insert into log_table1 values ('Log data', :new.value1,
:new.value2);
insert into log_table2 values ('Log data', :new.value1,
:new.value2);
end if;
END;

Is there any way to get what I need using one trigger?

Thanks so much,
Melissa
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Simply placing the select statement in a statement level trigger instead in
a row level trigger gives the solution. In addition, you need to store the
old en new values in something like a packaged pl/sql table, which you can
acces in the statement level trigger. Filling the pl/sql table occurs in the
row level trigger. Besides, the query is a little bit useless when
inserting, since in that case old values simply don't exists, i.e. equals
null (numofdocs whill alwasy equal 0 when inserting).
"M Mueller" <me*************@yahoo.com> schrieb im Newsbeitrag
news:ac**************************@posting.google.c om...
Hello -

I'm using Oracle 8i and am running into the infamous mutating table
error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
examples for getting around mutating table errors if you either need
access to the :old or the :new values within a trigger. But what if
you need access to both? Or is this even possible (I'm fairly new to
triggers).

I have to execute a select count(*) from trigger_table where
value1=:old.value1 and value2=:old.value2. Based on this result, log
files must be written utilizing the :new values. A simplified example
of my trigger is below:

CREATE OR REPLACE TRIGGER mut_trigger
AFTER INSERT OR UPDATE OF value1, value2
ON table_a
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
numofdocs number;
BEGIN
select count(*) into numofdocs from table_a where value1=:old.value1
and value2=:old.value2;
if (numofdocs > 0) then
insert into log_table1 values ('Log data', :new.value1,
:new.value2);
insert into log_table2 values ('Log data', :new.value1,
:new.value2);
end if;
END;

Is there any way to get what I need using one trigger?

Thanks so much,
Melissa

Jul 19 '05 #2

P: n/a
Simply placing the select statement in a statement level trigger instead in
a row level trigger gives the solution. In addition, you need to store the
old en new values in something like a packaged pl/sql table, which you can
acces in the statement level trigger. Filling the pl/sql table occurs in the
row level trigger. Besides, the query is a little bit useless when
inserting, since in that case old values simply don't exists, i.e. equals
null (numofdocs whill alwasy equal 0 when inserting).
"M Mueller" <me*************@yahoo.com> schrieb im Newsbeitrag
news:ac**************************@posting.google.c om...
Hello -

I'm using Oracle 8i and am running into the infamous mutating table
error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
examples for getting around mutating table errors if you either need
access to the :old or the :new values within a trigger. But what if
you need access to both? Or is this even possible (I'm fairly new to
triggers).

I have to execute a select count(*) from trigger_table where
value1=:old.value1 and value2=:old.value2. Based on this result, log
files must be written utilizing the :new values. A simplified example
of my trigger is below:

CREATE OR REPLACE TRIGGER mut_trigger
AFTER INSERT OR UPDATE OF value1, value2
ON table_a
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
numofdocs number;
BEGIN
select count(*) into numofdocs from table_a where value1=:old.value1
and value2=:old.value2;
if (numofdocs > 0) then
insert into log_table1 values ('Log data', :new.value1,
:new.value2);
insert into log_table2 values ('Log data', :new.value1,
:new.value2);
end if;
END;

Is there any way to get what I need using one trigger?

Thanks so much,
Melissa

Jul 19 '05 #3

P: n/a
Simply placing the select statement in a statement level trigger instead in
a row level trigger gives the solution. In addition, you need to store the
old en new values in something like a packaged pl/sql table, which you can
acces in the statement level trigger. Filling the pl/sql table occurs in the
row level trigger. Besides, the query is a little bit useless when
inserting, since in that case old values simply don't exists, i.e. equals
null (numofdocs whill alwasy equal 0 when inserting).
"M Mueller" <me*************@yahoo.com> schrieb im Newsbeitrag
news:ac**************************@posting.google.c om...
Hello -

I'm using Oracle 8i and am running into the infamous mutating table
error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
examples for getting around mutating table errors if you either need
access to the :old or the :new values within a trigger. But what if
you need access to both? Or is this even possible (I'm fairly new to
triggers).

I have to execute a select count(*) from trigger_table where
value1=:old.value1 and value2=:old.value2. Based on this result, log
files must be written utilizing the :new values. A simplified example
of my trigger is below:

CREATE OR REPLACE TRIGGER mut_trigger
AFTER INSERT OR UPDATE OF value1, value2
ON table_a
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
numofdocs number;
BEGIN
select count(*) into numofdocs from table_a where value1=:old.value1
and value2=:old.value2;
if (numofdocs > 0) then
insert into log_table1 values ('Log data', :new.value1,
:new.value2);
insert into log_table2 values ('Log data', :new.value1,
:new.value2);
end if;
END;

Is there any way to get what I need using one trigger?

Thanks so much,
Melissa

Jul 19 '05 #4

P: n/a
@#$$#%^%%&^
"M Mueller" <me*************@yahoo.com> wrote in message
news:ac**************************@posting.google.c om...
Hello -

I'm using Oracle 8i and am running into the infamous mutating table
error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen
examples for getting around mutating table errors if you either need
access to the :old or the :new values within a trigger. But what if
you need access to both? Or is this even possible (I'm fairly new to
triggers).

I have to execute a select count(*) from trigger_table where
value1=:old.value1 and value2=:old.value2. Based on this result, log
files must be written utilizing the :new values. A simplified example
of my trigger is below:

CREATE OR REPLACE TRIGGER mut_trigger
AFTER INSERT OR UPDATE OF value1, value2
ON table_a
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
numofdocs number;
BEGIN
select count(*) into numofdocs from table_a where value1=:old.value1
and value2=:old.value2;
if (numofdocs > 0) then
insert into log_table1 values ('Log data', :new.value1,
:new.value2);
insert into log_table2 values ('Log data', :new.value1,
:new.value2);
end if;
END;

Is there any way to get what I need using one trigger?

Thanks so much,
Melissa

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.