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 4 7036
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
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
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
@#$$#%^%%&^
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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...
|
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,...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |