472,980 Members | 2,176 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,980 software developers and data experts.

Mutating tables

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
4 7000
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
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
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
@#$$#%^%%&^
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Joel Jose | last post by:
agreed the 'mutation engine' by dark avenger was a real deadly master-code piece.., but then.. if one could create a self mutating code..in real time then i think it would just b the most...
2
by: robert | last post by:
i've found the solution threads on changing a column on insert. works fine. question: - will one package serve for all such triggers, or does there need to be a package defined to support...
4
by: M | last post by:
Hello, I have a very simple table, and want to create a trigger that updates the date column entry (with the current date), whenever a row gets modified. Is there a simple way of fixing this,...
0
by: hankr | last post by:
I have only a developer's level knowledge of Oracle, though I have access to DBA tools. Searches through the docs have not been fruitful, so I thought I'd try here. I am getting an error on a...
0
by: AYAN MUKHERJEE | last post by:
I have 2 tables. 1) emp_test, 2) newemp_test. I had created a trigger to insert automatic data in the table no. 2 , as soon as a row of information is inserted in table no. 1. The Trigger had been...
3
by: extremexpert | last post by:
Hai all, I have two tables like hdr and det. I would like to create the trigger for the situation ' 1.Delete Det table records, if any record is deleting in hdr table 2. Delte hdr table...
4
by: M Mueller | last post by:
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...
1
by: gangulajagan | last post by:
Could any one please explain me what is a Mutating Table Or Mutaing Trigger and how to overcome it? Are both Mutating trigger and Mutating Table one and same? Please Explain me with an example...
16
by: vamsioracle | last post by:
Can Someone help me how to avoid mutating error while using triggers. I work on oracle apps. I created a vacation rule such that responsibility is delegated to other person. These details are...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.