Hi Tom,

I was trying to implement the workaround mentioned in
your article on mutating tables. I succeeded when using
simply a scaler, but I'm stuck when I need to keep
a record. Here's the error I get:


insert into ppcalldata values ('999',999,1);
insert into ppcalldata values ('999',999,1)
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "MTR.SHADOW_MT_AIFER", line 3
ORA-04088: error during execution of trigger 'MTR.SHADOW_MT_AIFER'


Below is the SQL to set everything up. I tried, as much as possible to follow your example. And I tried to search for
"Reference to uninitialized composite" but had no luck.





drop table ppcalldata
/
create table ppcalldata (
ref_num NUMBER(15),
pnum VARCHAR2(30),
traffic_case int
)
/
create or replace type callIdentifier as object
( ref_num NUMBER(15),
pnum VARCHAR2(30)
)
/
-- attribute_datatype
--
-- This is any Oracle datatype except LONG, LONG RAW, NCHAR, NCLOB, NVARCHAR2,
-- ROWID, UROWID, the PL/SQL-specific types BINARY_INTEGER (and its subtypes),
-- BOOLEAN, PLS_INTEGER, RECORD, REF CURSOR, %TYPE, and %ROWTYPE, and types
-- defined inside a PL/SQL package.

--create or replace type arrayOfCalls as table of callIdentifier -- index by binary_integer

REM this package is used to maintain our state. We will save the rowids of newly
REM inserted / updated rows in this package. We declare 2 arrays -- one will
REM hold our new rows rowids (newRows). The other is used to reset this array,
REM it is an 'empty' array

create or replace package state_pkg
as
type arrayOfCalls is table of callIdentifier index by binary_integer;

newMTRows arrayOfCalls;
newCFRows arrayOfCalls;
empty arrayOfCalls;
end;
/

REM We must set the state of the above package to some known, consistent state
REM before we being processing the row triggers. This trigger is mandatory,
REM we *cannot* rely on the AFTER trigger to reset the package state. This
REM is because during a multi-row insert or update, the ROW trigger may fire
REM but the AFTER tirgger does not have to fire -- if the second row in an update
REM fails due to some constraint error -- the row trigger will have fired 2 times
REM but the AFTER trigger (which we relied on to reset the package) will never fire.
REM That would leave 2 erroneous rowids in the newRows array for the next insert/update
REM to see. Therefore, before the insert / update takes place, we 'reset'

create or replace trigger shadow_MT_bi
before insert or update on ppcalldata
begin
state_pkg.newMTRows := state_pkg.empty;
state_pkg.newCFRows := state_pkg.empty;
end;
/

REM This trigger simply captures the rowid of the affected row and
REM saves it in the newRows array.

create or replace trigger shadow_MT_aifer
after insert or update on ppcalldata for each row
begin
if ( :new.traffic_case = 1 ) then -- CF
state_pkg.newCFRows( state_pkg.newCFRows.count+1 ).ref_num := :new.ref_num;
state_pkg.newCFRows( state_pkg.newCFRows.count ).pnum := :new.pnum;
elsif ( :new.traffic_case = 2 ) then -- MT
state_pkg.newMTRows( state_pkg.newMTRows.count+1 ).ref_num := :new.ref_num;
state_pkg.newMTRows( state_pkg.newMTRows.count ).pnum := :new.pnum;
end if;
end;
/


REM this trigger processes the new rows. We simply loop over the newRows
REM array processing each newly inserted/modified row in turn.

create or replace trigger shadow_MT_ai
after insert or update on ppcalldata
begin
for i in 1 .. state_pkg.newCFRows.count loop
delete ppcalldata where
ref_num = state_pkg.newCFRows(i).ref_num and
pnum = state_pkg.newCFRows(i).pnum and
traffic_case = 2;
end loop;



for i in 1 .. state_pkg.newMTRows.count loop
delete ppcalldata a
where a.ref_num = state_pkg.newMTRows(i).ref_num and
a.pnum = state_pkg.newMTRows(i).pnum and
a.traffic_case = 2 and exists
( select 0
from ppcalldata b
where b.ref_num = a.ref_num and
b.pnum = a.pnum and
b.traffic_case = 1
);
end loop;
end;
/





If you could help, that would be great.

Thanks Tom.

Steve

Hi Tom

Thanks for your response to the above Infamous Mutating Error...

Could you please give us more clarification on why you added this Recursion check on your example above , but there's no such a check on the link you have given above...

I guess the reason is that so that i avoid updation of the rows that are being modified from within my procedure which is called in the AFTER INSERT STATEMENT Trigger...

Right?

Hi Tom,

This is a very useful workaround on updating. Is there a workaround for firing a trigger to update the same row after the row is inserted into a table? Say, in your table T, after an application does "insert into t values (2, 'Y', 'N');", the result should be
SQL> select * from t where id = 2;
ID  L   P
--  --  --
2   Y   N
But, we want it to be
ID  L   P
--  --  --
2   Y   Y
It sounds strange, and really the application is wrong. We do not have the source code to modify the application, so we need to write a tigger on the table to accomplish that. Can it be done by trigger?

Thank you very much.
 

Tom,

The "BEFORE insert" trigger makes my day meaningful. Thank you so much.

Regards,

Jeff

Hi tom,

 If I have a table 

city 

city_code   varchar2(5)(PK)
city_name   varchar2(30) NOT NULL (unique)
city_state_cd  varchar2(5) (FK state table) NULL
city_county_cd varchar2(5) (FK country Table) NOT NULL


NOTE : if state is null then after that insert
1.)  there must not be any combination(of city and country)
    allowed for insert or update
2.)  if there is atleast one state already availabe in 
      the table for the combination of city and country 
       then null state is not allwoed for that combination
      of city and state
   

I have function as......


CREATE OR REPLACE FUNCTION city_check(p_city varchar2,p_state varchar2,p_country varchar2)
RETURN number IS

stv_city varchar2(200);
stv_stp  varchar2(200);
stv_cny  varchar2(200);

BEGIN

    SELECT     city_name,city_stp_code,city_cny_code
    INTO       stv_city, stv_stp,stv_cny
    FROM       city
    WHERE      city_name     = p_city
    AND     city_cny_code    = p_country; 


    IF(  stv_stp is not null) then
            
        IF (stv_stp = p_state) THEN
                    RAISE DUP_VAL_ON_INDEX;
            END IF;  
            return(1);
         ELSE
        RAISE DUP_VAL_ON_INDEX;
         END IF;

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
         RAISE;
   WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('NO-DATA-FOUND : ' ||SQLERRM);
      return(1);
   WHEN too_many_rows THEN  
    DBMS_OUTPUT.PUT_LINE('TOO-MANY-ROWS : ' ||SQLERRM);
    IF(p_state is null) then
        RAISE;        
    END IF;
        BEGIN

        SELECT     city_name,city_stp_code,city_cny_code
        INTO       stv_city, stv_stp,stv_cny
        FROM       city
        WHERE      city_name     = p_city
        AND     city_stp_code   = p_state 
        AND     city_cny_code    = p_country; 

        RAISE DUP_VAL_ON_INDEX; 
    EXCEPTION
         WHEN no_data_found THEN
         return(1);
         WHEN others THEN 
         RAISE; 
    END;
   WHEN others THEN   
       DBMS_OUTPUT.PUT_LINE('others : ' || SQLERRM);
       RAISE;
END;
/
show err


I have row level trigger that 

  
CREATE OR REPLACE 
TRIGGER  city_insertupdate_row
 BEFORE INSERT or UPDATE
 ON city
 FOR EACH ROW
DECLARE
     v_temp NUMBER;
 BEGIN
      v_temp := city_check(:new.city_name,:new.city_stp_code,:new.city_cny_code);
  
      IF(v_temp = -1) THEN
            RAISE_APPLICATION_ERROR(-20002,'Record Already Exists !!');
      END IF;
 
END city_insertupdate_row;
/

OUTPUT
---------

SQL>  exec p_city.update_city('AA','Middletown',NULL,'US',:msg_cd,:msg_str);
check_city:others : ORA-04091: table DATADEPOT.CITY is mutating, trigger/function may not see it

CI CITY_NAME                      CI CI
-- ------------------------------ -- --
LV Las Vegas                      NV US
LG 123456789012345678901234567890    LG
AB A12345678912345678901234567890    US
M1 METUCHAN                       NJ US
MD Middletown                        US
XX x y z                          NY US
AA AA                                GB

can you help me with that ?

thanks, 

Hi Tom,
I have got three tables

Create table TB_IW_WORKITEM_INFO
(
ID VARCHAR2(44),
SOURCE_QUEUE VARCHAR2(20),
DEST_QUEUE VARCHAR2(20),
TIME_SPENT VARCHAR2(26),
NATURE NUMBER(3),
STATUS VARCHAR2(60),
QUEUE_DATE DATE,
PROCESSOR VARCHAR2(12),
CFDT_QUEUENAME VARCHAR2(50) default ('')
)

create table TB_IW_WORKITEM
(
WORKITEM_ID VARCHAR2(44) not null,
WORKITEM_NAME VARCHAR2(44) not null,
BRANCH_CODE NUMBER(5) not null,
RLABEL2 VARCHAR2(32),
RLABEL3 VARCHAR2(32),
PRODUCT VARCHAR2(32),
RLABEL4 VARCHAR2(32),
RLABEL5 VARCHAR2(32),
IW_REFERENCE NUMBER(10),
REFERENCE_NUMBER NUMBER(10),
MAKER_DATE DATE not null,
MAKER_NAME VARCHAR2(12) not null,
CLARIFYECI VARCHAR2(12),
EXCEPTIONS_I NUMBER(10),
EXCEPTIONS_E NUMBER(10),
EXCEPTIONS_C NUMBER(10),
RLABEL1 VARCHAR2(32),
COUNTRY VARCHAR2(32) default (' ') not null,
CITY VARCHAR2(32) default (' ') not null,
RLABEL8 VARCHAR2(32),
RLABEL7 VARCHAR2(32),
FOLDER_NUMBER VARCHAR2(30),
RLABEL6 VARCHAR2(32),
RLABEL9 VARCHAR2(32),
PARENT_WORKITEM_ID VARCHAR2(44),
PARENT_WORKITEM_NM VARCHAR2(44),
TXN_IMG_AUTH_STAT VARCHAR2(1) default ('A') not null,
ACTION_TAKEN_DATE DATE
)
create table TB_IW_WANG_CITIDOCS
(
WORKITEM_ID VARCHAR2(44) not null,
CITIDOCS_CABINET_INDEX NUMBER(10) not null,
CITIDOCS_REFERENCE_NUMBER NUMBER(10) not null,
WORKITEM_TYPE NUMBER(3) default (0) not null,
ATTACHMENT_ID NUMBER(10) default (0) not null,
IMG_AUTH_STAT VARCHAR2(1) default ('A') not null
)


I have got a (14) triggers on the TB_IW_WORKITEM_INFO table. 3 of them are disabled. I have used three separate packages to maintain the state. I used those packages after seeing your example on table mutation.

Whenever I insert a row, I get an error stating that
ORA-04063: has errors
ORA-04063: package body "ORBIDATA.WKITEM_COUNT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "ORBIDATA.TRG_BINS_WKITEM_COUNT", line 2
ORA-04088: error during execution of trigger 'ORBIDATA.TRG_BINS_WKITEM_COUNT'

Here is the code of my package. All the three packages have the same code, but its this one that is giving the error.

create or replace package WKITEM_COUNT_PKG
As

-- Created : 12/10/2002 4:37:51 PM
-- Purpose : To Maintain the state during updateworkitemscount
-- Used by the trigger trg_updateworkitemscount on TB_IW_WORKITEM_INFO table
type ridArray is table of rowid index by binary_integer;
newRows ridArray;
empty ridArray;
avoid_recursion boolean default false;
end;

Here is the text of the trigger which is causing the problem.
create or replace trigger trg_bins_wkitem_count
before insert or update on TB_IW_WORKITEM_INFO
begin
if ( not wkitem_count_pkg.avoid_recursion ) then
wkitem_count_pkg.newRows := wkitem_count_pkg.empty;
end if;
end;



Could you please help me out? Do you need more information?

Cheers!
Abhijit

what is hard to understand about:

<b>
ORA-04063: package body "ORBIDATA.WKITEM_COUNT_PKG" has errors
</b>
????

go into sqlplus and issue:

SQL> show errors package body wkitem_count_pkg


you have a package body for that guy and it is invalid!  if you did not mean to have a package body, drop the package and recreate it.


A house built of cards is a fragile thing indeed.  14 triggers, 3 packages to maintain state -- trying to avoid mutating tables.  Ugg, sounds really complex.... 

Hi Tom,
Thanks for your prompt reply. I dropped the package and re-created it. However heeding your warning, I decided to do away with the third-package (also avoided three triggers in this process). Now I have got two packages and 11 triggers.

Will test and let you know about the results.
My project manager is of the opinion that existing code / objects should not be disturbted under any circumstances. Esp once they have been reviewed and frozen. So all the developers of this team simply create new objects. Hence so many triggers. I have now attempted to reverse this trend, by modifying one of the existing triggers. Lets see what happens.

Cheers!
Abhijit

Hi Tom,

I ran into this problem with the following code:

declare
Type varchar2_table_type IS TABLE OF VARCHAR2 (50) INDEX BY BINARY_INTEGER;
lt_denial_str varchar2_table_type;
type number_table_type is table of number index by binary_integer;
v_table1 dbms_sql.varchar2_table;
n_table1 dbms_sql.number_table;
...
...
SELECT to_number (substr (ad_ac.lnum, 6)) lnum,
decode(ad_ac.excsvobl,'Y','1','0') ||
decode(ad_ac.incexcin,'Y','1','0') ||
decode(ad_ac.empleng,'Y','2','0') ||
decode(ad_ac.empltemp,'Y','2','0') ||
decode(ad_ac.bankrupt,'Y','3','0') ||
decode(ad_ac.crdt_oth,'Y','3','0') ||
decode(ad_ac.delcrdob,'Y','3','0') ||
decode(ad_ac.garnish,'Y','3','0') ||
decode(ad_ac.limcrdt,'Y','3','0') ||
decode(ad_ac.otsdsour,'Y','3','0') ||
decode(ad_ac.paymtuna,'Y','3','0') ||
decode(ad_ac.poorcred,'Y','3','0') ||
decode(ad_ac.collsuff,'Y','4','0') ||
decode(ad_ac.cashlack,'Y','5','0') ||
decode(ad_ac.insuffnd,'Y','5','0') ||
decode(ad_ac.insufinc,'Y','5','0') ||
decode(ad_ac.vrfycrdt,'Y','6','0') ||
decode(ad_ac.vrfyempl,'Y','6','0') ||
decode(ad_ac.vrfyincm,'Y','6','0') ||
decode(ad_ac.vrfyres,'Y','6','0') ||
decode(ad_ac.appincmp,'Y','7','0') ||
decode(ad_ac.crdtref,'Y','7','0') ||
decode(ad_ac.insuffcr,'Y','7','0') ||
decode(ad_ac.insuffle,'Y','7','0') ||
decode(ad_ac.nofile,'Y','7','0') ||
decode(ad_ac.denfhlmc,'Y','9','0') ||
decode(ad_ac.denothr1,'Y','9','0') ||
decode(ad_ac.deny_hud,'Y','9','0') ||
decode(ad_ac.deny_va,'Y','9','0') ||
decode(ad_ac.denyfnma,'Y','9','0') ||
decode(ad_ac.nocredit,'Y','9','0') ||
decode(ad_ac.otherbx1,'Y','9','0') ||
decode(ad_ac.resleng,'Y','9','0') ||
decode(ad_ac.tempres,'Y','9','0')
BULK COLLECT
INTO n_table1, v_table1
FROM ADV_ACTN ad_ac, viewpipe_temp v
WHERE ad_ac.lnum = v.lnum;
--
for i in 1 .. n_table1.count
loop
lt_denial_str (n_table1(i)) := v_table1 (i);
n_table1.delete(i);
v_table1.delete(i);
end loop;
--
end;

This problem seems to be intermittent however, I've run into it only once so far, in all my executions of this same piece of code. Per your explanation, it might be as a result of mixing plsql types and sql types, but that isn't the case here. Also, the query does generate output, around 20K records, so the two tables which are bulk collected into are populated. Any feedback would be useful. Thanks.

Kashif

1606456 is the bug number which documents the above problem. The fix suggested is to upgrade to 8.1.7.

Kashif

Consider a table like this:

order_id number
<some composite unique key that identifies the uniqueness of a row besides order_id) say comp_uq
Order_type number
Order_status varchar2(50)

Order_id is an auto_generated primary key
Order_type column can have only two possible values (1 or2)
Order_status column can have only two possible values ('Processing' or 'Completed')

Now, an order_type of 1 should have a corresponding 2 (comparing comp_uq)

Order_status column for a order_type of 2 can not be modified through the app.


Problem: When ORDER_STATUS column for a order_type of 1 is updated to 2....
find the order_id through comp_uq keys and order_type 2...and update the order_status to 2 if it's 1.


Question:
Can this be done through a trigger...because it will be updating the same column that the trigger is on.
If yes How?

Thanks

The problem is that the trigger would execute when an order of order_type 1 is being updated to "PROCESSING"...At that point I have to find the counterpart order_type of 2 and update the order_status to "PROCESSING"...
This requires querying the ORDER table...and getting into a mutating table error...

Oracle 8.1.5, PB 7.0.

i have the work of debugging the following delicate ( bad ?) piece of code and determine what caused what.

here it is:

A ( id -> pk, other columns...)
B ( id -> pk references A(id) on delete cascade , other columns...)
C ( id -> pk references B(id) on delete cascade , other columns...)

in addition to 'cascade delete' there is:

on delete trigger of A:
delete from b where id = ...

on delete trigger of B:
delete from C where id = ...

try a deletion on A or B and the triggers happily mutate !

so the PB code does the following:
disable the triggers.
delete from c where id = ...
delete from b where id = ...
delete from a where id = ...
enable the triggers.
the deletion goes from the child to the parent step by step instead of just:
delete from a where id = ...

when this was run the log showed mutating trigger errors for a huge no. of records (in production).

in development environment:
fortunately there seems to be enough error handling, writing each and every error into a log file.

so, if the disable trigger step doesnt go through, it is logged and the process is terminated because it will surely fire the mutating table error.

i confirmed this by running it as a user who doesnt have alter table priv ( to disable the trigger ).
and sure enough the process terminated and the log showed 'insufficient privs' error .
i am unable to recreate the production issue.

any ideas on what could have happened , so that the trigger is not disabled but yet escaped error handling (logging) ?
how do you suggest we clean up this kind of code ?
Thanks !

i am confused too about the triggers part. the tables are ok ( i guess ) because they contain other information not present in the parent.
that the design as well as the code is a mess, is clear.
temporarily, i am just trying to determine how this mutating table error got logged.
(more out of curiosity as i am not able to understand how it happened!)
so i'll try to frame the "yet escaped the error handling part" better here:

the code goes like this:

disable the triggers.
if error then
log the error and halt the process
else
proceed with the deletions...
end if

so it could only be one of the following two:
a) error while disabling, log that error and halt the process.
b) disabling is successful, so the deletions go through. or rather, there should not be any mutating table error.

The log shows the mutating table error.
so its not option a) because the process did not stop.
its not option b) because i got the mutating table error which means -> trigger disabling unsuccessful (again a guess here). but if the triggers were not disabled then it should have logged that and halted the process.....
my confusion goes in a cycle :-)

question :- under what circumstances the ' disable trigger ' statement failed but did not throw any error and proceeded to the next step ?
what happened here ?

Tom,

You said: "why bother with 3 tables even if the primary keys are the fkeys -- looks like there should be one table A and no B or C."

This is often done by database designers when B is a subtype of A. For example, Manager might be a subtype of Employee, and Managers have a lot of extra attributes and relationships. Also, only 5% of Employees are Managers. The designer may have decided to hold the Manager data in a separate table for performance reasons.

Now, since you know more about performance than most designers and have said the above I wonder whether you think that such a design decision is NEVER valid - or would you agree it might make sense sometimes?

Yes, they actually did that somewhere I worked a while ago. System testing was showing performance to be poor, so the project's Oracle "expert" decided that some drastic changes were required. This involved splitting what had been one table with maybe 150 columns into about 6 smaller tables like:
MAIN_TABLE
- SUBTYPE 1
- SUBSUBTYPE 1A
- SUBSUBTYPE 1B
- SUBTYPE 2
- SUBTYPE 3

This was the same guy who I've mentioned before, who wanted to drop all the check constraints to improve performance!

Needless to say the changes were approved and made on the basis that they "might" help, without any testing being done to verify the claim.

Hi Tom,

I have a summary table where i summarize the activity of an item into one record monthly. In this table there is an opening balance for every item brought forward form the previous period (ia_ob_qty, ia_ob_cost), along with current month transaction (ia_mnth_qty, ia_mnth_cost). I'm trying to do a database trigger that whenever there is an insert or update on an item, trigger should fire and update the opening balance of the same item but for the next period. But I'm getting mutating error. My example is as below:


The Table is
desc INV_ITEM_ACTVTY

Name Null? Type
------------------------------- -------- ----
IA_COMPANY NOT NULL NUMBER(2)
IA_YEAR NOT NULL NUMBER(4)
IA_PERIOD NOT NULL NUMBER(2)
IA_ITEM NOT NULL NUMBER(10)
IA_WRHS NOT NULL VARCHAR2(5)
IA_BATCH_NO NOT NULL VARCHAR2(25)
IA_OB_QTY NOT NULL NUMBER(12,2)
IA_OB_COST NOT NULL NUMBER(12,2)
IA_MNTH_QTY NOT NULL NUMBER(12,2)
IA_MNTH_COST NOT NULL NUMBER(12,2)


Trigger

CREATE OR REPLACE TRIGGER "AIU_INV_ITEM_ACTVTY"
AFTER INSERT OR UPDATE
ON "INV_ITEM_ACTVTY"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

DECLARE
t_cur_prd NUMBER(6);
t_nxt_year NUMBER(4);
t_nxt_prd NUMBER(2);
t_qty NUMBER(12,2):=0;
t_cost NUMBER(12,2):=0;

BEGIN
select to_char(sysdate,'yyyymm') into t_cur_prd from dual;

IF :new.ia_period < 12 THEN
t_nxt_year := :new.ia_year;
t_nxt_prd := :new.ia_period + 1 ;
ELSE
t_nxt_year := :new.ia_year + 1;
t_nxt_prd := 1 ;
END IF;

t_qty := :new.ia_ob_qty + :new.ia_mnth_qty;
t_cost := :new.ia_ob_cost + :new.ia_mnth_cost;

IF (:new.ia_year*100+:new.ia_period) <= t_cur_prd THEN
BEGIN
update inv_item_actvty
set ia_ob_qty = t_qty,
ia_ob_cost = t_cost
where ia_company = :new.ia_company
and ia_year = t_nxt_year
and ia_period = t_nxt_prd
and ia_item = :new.ia_item
and ia_wrhs = :new.ia_wrhs
and ia_batch_no = :new.ia_batch_no ;

if sql%notfound then
insert into inv_item_actvty
(IA_COMPANY, IA_YEAR, IA_PERIOD, IA_ITEM, IA_WRHS,
IA_BATCH_NO, IA_OB_QTY, IA_OB_COST, IA_MNTH_QTY, IA_MNTH_COST)
values(:new.ia_company, t_nxt_year, t_nxt_prd, :new.ia_item,
:new.ia_wrhs,:new.ia_batch_no, t_qty, t_cost, 0, 0)
end if;
END;
END IF;
END;
/


When i issue an update command the following error arise

update INV_ITEM_ACTVTY set ia_mnth_qty=ia_mnth_qty, ia_mnth_cost=ia_mnth_cost
where ia_year=2001 and ia_period=11;

update INV_ITEM_ACTVTY set ia_mnth_qty=ia_mnth_qty, ia_mnth_cost=ia_mnth_cost
*
ERROR at line 1:
ORA-04091: table INV_ITEM_ACTVTY is mutating, trigger/function may not see it
ORA-06512: at "AIU_INV_ITEM_ACTVTY", line 24
ORA-04088: error during execution of trigger 'AIU_INV_ITEM_ACTVTY'


Am I using the right approach or do you have another suggestion.

Your advice is appreciated.

Regards

Dear Tom,
I am working in a datawarehouse application. I am having a TRANSACTIONS table. TRANSACTIONS table is loaded thru sql*loader

After every daily run i need to fire a query for which output should go to a text file.

For example structure of TRANSACTIONS TABLE is
Busdate date,
Acctno varchar2(40)

I created a trigger as follows

create or replace trigger amla
after insert on transactions
declare
pragma autonomous_transaction;
l_rows number;
busdate date;
begin
select max(busdate) into busdate from transactions;
l_rows := dump_csv('select * from transactions where busdate=(select max(busdate) from transactions)','c:\oracle\admin\dwdev\udump','abc'||busdate);
dbms_output.put_line(to_char(l_rows)||' rows extracted to ascii file');
end;
/

My problem is that i am getting one day late data. If the data is loaded for 10-apr , i get the text file for 9-apr, similarly if the data is loaded for 11-apr i get the text file for 10-apr.

When i put after insert trigger, it should fire after the insert is over in the table. If the data load is over for 10-apr, trigger should fire , get the max date as 10-apr and give the text file for 10-apr.

Can u please what is wrong in the trigger and suggest a workaround.

Regards,
Anil

thats funny.

I write about this in my book -- you used an automous transaction for some reason.   The autonomous transaction has very very very few uses (and this is not one of them)


It is "as if" you did "host( 'sqlplus scott/tiger @dump_table.sql' )" from your trigger.  your trigger is running in totally separate transaction from the parent transaction that fired it.  Hence, just like that sqlplus session would

a) not be blocked by your uncommitted modifications
b) not be able to SEE your uncommitted modifications

your trigger cannot see them either.  Consider:


ops$tkyte@ORA920> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t select * from all_users where rownum = 1;

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> variable atrans refcursor
ops$tkyte@ORA920> variable sameTrans refcursor
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          PRAGMA autonomous_transaction;
  3  begin
  4          open :atrans for select * from t;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2          open :sameTrans for select * from t;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> print atrans

no rows selected

ops$tkyte@ORA920> print sameTrans

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 07-MAR-03



You did it to yourself!

Also, considering that sqlldr BATCH inserts and commits -- your trigger is getting fired for each BATCH.  do you really want that -- do you want to run this over and over and over and over and over?  (or just once).


So, ask yourself "what was my intention with this autonomous transaction thing, why did I put that in there??"

second question "do i really want to dump the table over and over and over -- or just once, after the load"


Here is a suggestion -- it seems you have a file you loaded, you seem to be dumping that data right back out.  Why wouldn't you just hang onto the file you loaded???

Suggestion 2 -- lose the trigger, modify the script that calls sqlldr right now to have a call to sqlplus to dump the file instead of a trigger.  It gets done ONCE that way. 

If I had a penny for every time I've seen this lately on forums...

Q: I'm getting mutating error in trigger - help

A: Use PRAGMA AUTONOMOUS_TRANSACTION

...I'd have a few quid!

Please refer to the above problem regarding TRANSACTIONS table and TRIGGER amla.

You are absolutely right, i forget the consideration that sqlldr will insert in batches and trigger will fire after every commit.

In fact the load is about 1 million rows for this table.

In our datawarehouse we load several tables. I had to run a query on TRANSACTIONS table after every daily run. TRANSACTIONS table has a busdate which signifies loaddate. I can do it only with a trigger so that after all the 1 million rows are inserted the query runs and gives output to a text file.

I can't use dbms_job as the time of load is not fixed. If i make the query time based, then i have a problem. So i thought of putting a trigger so that after the load is successful the query runs automatically and put the output in a text file irespective of the time when the load was done.

Can u suggest some workaround. I am using oracle 8.1.7

Regards,
Anil

Just wonder if the state package will cause any issue in web enviornment when connection pooling is enabled.

Are you refering to the use of avoid_recursion flag? Can you elaborate more?

Hello,
This question may be a repitative one. If so pls give me the link so that I can go thru.
Here Im not trying to compare SQL Server and Oracle. Since I've worked on both Im bit curious. I always wondered why in Oracle trigger we've mutating concept and same thing we dont have something like that in SQL Server.
But I liked the Oracle way. Infact in one of project I was on SQL Server I was trying to debug someone's code and it took me almost a day to find out why value was changing then I learnt that in SQL Server you can change the column value in trigger written on that table.
Is there any other RDBMS which has this concept or its only in Oracle ? Also why Oracle has given this ?I think it wud be false if I say it as a restriction but we should call it an an feature.

At the very beginning you said:
"Can your logic be as simple as:
begin
:new.processed_status := some_procedure( :new.id,
:new.processed_status );
end;
/"
But a procedure's RETURN cannot, I understand, return a value, so is some_procedure() technically a function, or could it somehow set :new.processed_status using an OUT parameter, or do I misunderstand :new.processed_status is being set to the return value from a procedure here?

Isn't concern warranted if multiple users store their update rowids in the same package variable(s) simultaneously (ridArray, which is flushed before each use in this case: </code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
Or am I missing something fundamental here about scope?
Thanks in advance.
And thanks again for the best web site in the known universe!!

What you said at the very beginning worked in spades for me.
I cannot describe how good it is to see such simple syntax.
Question: I changed your trigger from a before update,
to before insert or update.
To:
create or replace trigger T_trigger
before INSERT OR UPDATE on T for each row
begin
:new.processed_field := a_procedure( :new.read_field);
end;
/
It sure seems to work well, and I have a static Java method which calls java.net.URLEncoder.encode behind the function.
Should I be concerned?
Thanks again, and again and again.....

I don't think this is a "mutating table" issue but I'm not sure how else to address it. I've boiled it down to the following test case but can give you more details if you need them. We are running 8.1.7. I start by creating the following table and trigger.

create table t (text_col varchar2(10) not null);

CREATE OR REPLACE TRIGGER ti
BEFORE INSERT
ON t
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:new.text_col := substr(:new.text_col, 2);
END;

Then I execute each of the following inserts.

insert into t select 'XXX' from all_tables where rownum <= 5;

insert into t select rownum||rownum from all_tables where rownum <=5;

The first one fails and the second one does not. I can make the first one not fail by increasing the number of X's or decreasing the 5 to 2. It seems that in the trigger, if I send 'XXX' it eats them up until there are none and then fusses but if I send rownum||rownum, it picks off the first and leaves the rest. What am I missing?

cannot reproduce, what are we looking for here?

ops$tkyte@ORA817DEV> create table t (text_col varchar2(10) not null);
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> CREATE OR REPLACE TRIGGER ti
  2  BEFORE INSERT
  3  ON t
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7    :new.text_col := substr(:new.text_col, 2);
  8  END;
  9  /
 
Trigger created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t select 'XXX' from all_tables where rownum <= 5;
 
5 rows created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t select rownum||rownum from all_tables where rownum <=5;
 
5 rows created.
 
ops$tkyte@ORA817DEV> select * from t;
 
TEXT_COL
----------
XX
XX
XX
XX
XX
1
2
3
4
5
 
10 rows selected.
 
ops$tkyte@ORA817DEV> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
 

Odd. Here is what I get from the inserts (sorry I didn't send this earlier) and why I'm confused. I expected the same results you got. 


SQL>  select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> insert into t select rownum||rownum from all_tables where rownum <=5;

5 rows created.

SQL> select * from t;

TEXT_COL
------------------------------
2
3
4
5
1

SQL> insert into t select 'XXX' from all_tables where rownum <= 5;
insert into t select 'XXX' from all_tables where rownum <= 5
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DHANZLIK"."T"."TEXT_COL")


SQL>  insert into t select 'XXX' from all_tables where rownum <= 2;

2 rows created.

SQL> select * from t;

TEXT_COL
------------------------------
2
3
4
5
XX
X
1

7 rows selected.

SQL> 


 

Here is the entire script. I had to drop the table first.

SQL> drop table t;

Table dropped.

SQL> select * from v$version;

BANNER
----------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Prod
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>  create table t (text_col varchar2(10) not null);

Table created.

SQL>  CREATE OR REPLACE TRIGGER ti
  2  BEFORE INSERT
  3  ON t
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7    :new.text_col := substr(:new.text_col, 2);
  8  END;
  9  /

Trigger created.

SQL> insert into t select rownum||rownum from all_tables w

5 rows created.

SQL> insert into t select 'XXX' from all_tables where rown
insert into t select 'XXX' from all_tables where rownum <=
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DHANZLIK"."T"."TEXT_C


SQL> 
SQL>  insert into t select 'XXX' from all_tables where row

2 rows created.

SQL> select * from t
  2  /

TEXT_COL
----------
1
2
3
4
5
XX
X

7 rows selected.

SQL> 

Thanks for any insight you might have in this. 

'XXX' is a constant, rownum is not.
For the 1st. row it starts as 'XXX' and substr( :new, 2 ) sets it to 'XX' first time through. 2nd time through, the value, now at 'XX' getsd substring'ed to 'X'. If you had more than <=2 rows, your string would become a null and fail the column's NOT NULL constraint.

Boy I am just screwing up all over. It looks like the last time I pasted, some of the lines got cut off. The reason the 'X' and 'XX' got in the table was because I ran the insert again but changed the where clause to rownum <= 2. That doesn't generate the error and (thank you Alfonso) I can see that it is cutting off my constant. I don't understand why and why it doesn't for you, Tom. I'll try the trace thing and get back to you.

Thanks.

It doesn't work that way for anyone in the world EXCEPT for you!  There is something else afoot here in your database.  


another thing -- can you stick into there:

dbms_output.put_line( 'before rowid = ' || :new.rowid || ' text = ' || :new.text_col );

your substr....


dbms_output.put_line( 'after rowid = ' || :new.rowid || ' text = ' || :new.text_col );


and set serveroutput on, then run your insert and if the dbms_output.putlines don't appear -- please just

SQL> exec null;

that'll dump them out.  lets get a little debug there. 

In my case I get the same result as Tom. Haven't looked further into it, but it could be some setting in your database that makes the trigger treat the constant value almost as a "global" rather than local to the row processed. Or perhaps as a stmt trigger instead of row trigger, somehow.

Here is what I executed followed by the abridged trace file (the whole file would not fit). I modified the insert statement and re-ran it with "where rownum <=2" and the output seems to reflect that it is shortening the 'XXX' string as each record is inserted. I should have put an exec null; right aftert the error occured but didn't so the output generated by the successful two rows is preceded by the output (6 lines) generated by the errored insert.  

SQL> alter session set sql_trace=true
  2  /

Session altered.

SQL> drop table t;

Table dropped.

SQL> create table t (text_col varchar2(10) not null);

Table created.

SQL> CREATE OR REPLACE TRIGGER ti
  2  BEFORE INSERT
  3  ON t
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7    dbms_output.put_line( 'before rowid = ' || :new.rowid || ' text = ' || :new.text_col ); 
  8    :new.text_col := substr(:new.text_col, 2);
  9      dbms_output.put_line( 'after rowid = ' || :new.rowid || ' text = ' || :new.text_col );
 10  END;
 11  /

Trigger created.

SQL> set serveroutput on;
SQL> insert into t select rownum||rownum from all_tables where rownum <=5;
before rowid = AAAAAAAAAAAAAAAAAA text = 11
after rowid = AAAAAAAAAAAAAAAAAA text = 1
before rowid = AAAAAAAAAAAAAAAAAA text = 22
after rowid = AAAAAAAAAAAAAAAAAA text = 2
before rowid = AAAAAAAAAAAAAAAAAA text = 33
after rowid = AAAAAAAAAAAAAAAAAA text = 3
before rowid = AAAAAAAAAAAAAAAAAA text = 44
after rowid = AAAAAAAAAAAAAAAAAA text = 4
before rowid = AAAAAAAAAAAAAAAAAA text = 55
after rowid = AAAAAAAAAAAAAAAAAA text = 5

5 rows created.

SQL> insert into t select 'XXX' from all_tables where rownum <= 5;
insert into t select 'XXX' from all_tables where rownum <= 5
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DHANZLIK"."T"."TEXT_COL")


SQL> 
SQL> insert into t select 'XXX' from all_tables where rownum <= 2;
before rowid = AAAAAAAAAAAAAAAAAA text = XXX
after rowid = AAAAAAAAAAAAAAAAAA text = XX
before rowid = AAAAAAAAAAAAAAAAAA text = XX
after rowid = AAAAAAAAAAAAAAAAAA text = X
before rowid = AAAAAAAAAAAAAAAAAA text = X
after rowid = AAAAAAAAAAAAAAAAAA text =
before rowid = AAAAAAAAAAAAAAAAAA text = XXX
after rowid = AAAAAAAAAAAAAAAAAA text = XX
before rowid = AAAAAAAAAAAAAAAAAA text = XX
after rowid = AAAAAAAAAAAAAAAAAA text = X

2 rows created.

SQL> select * from t;

TEXT_COL
----------
1
2
3
4
5
XX
X

7 rows selected.

SQL> 


I've abbreviated the corresponding trace file to show only the top (up to the alter session) and the bottom (beginning with the "insert into t select rownum||rownum ...":

/u02/db1/oracle/admin/D01/bdump/s000_129_d01.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
ORACLE_HOME = /u02/db1/oracle/product/8.1.7
System name:    HP-UX
Node name:    hp10
Release:    B.11.00
Version:    U
Machine:    9000/800
Instance name: D01
Redo thread mounted by this instance: 1
Oracle process number: 9
Unix process pid: 129, image: oracle@hp10 (S000)

*** 2004-02-17 09:54:25.350
*** SESSION ID:(19.213) 2004-02-17 09:54:25.338
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=376 oct=42 lid=376 tim=328784135 hv=3732290820 ad='ddd6fb0'
alter session set sql_trace=true
END OF STMT
.
.
<Abridged here> 
.
.
=====================#
PARSING IN CURSOR #1 len=78 dep=0 uid=376 oct=2 lid=376 tim=328790998 hv=103470991 ad='db83c90'
insert into t select rownum||rownum from all_tables where rownum <=:"SYS_B_0"
END OF STMT
PARSE #1:c=7,e=8,p=4,cr=232,cu=0,mis=1,r=0,dep=0,og=0,tim=328790998
EXEC #1:c=4,e=15,p=80,cr=1611,cu=16,mis=0,r=5,dep=0,og=4,tim=328791013
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=376 oct=47 lid=376 tim=328791015 hv=1697159799 ad='db55ba0'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=328791015
EXEC #3:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=328791015
STAT #1 id=1 cnt=6 pid=0 pos=0 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=5 pid=1 pos=1 obj=0 op='FILTER '
STAT #1 id=3 cnt=5 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=4 cnt=5 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=5 cnt=5 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=6 cnt=5 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=7 cnt=5 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=8 cnt=5 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=9 cnt=5 pid=8 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=10 cnt=947 pid=9 pos=1 obj=6057 op='TABLE ACCESS FULL OBJ$ '
STAT #1 id=11 cnt=951 pid=9 pos=2 obj=6047 op='TABLE ACCESS CLUSTER TAB$ '
STAT #1 id=12 cnt=1359 pid=11 pos=1 obj=6046 op='INDEX UNIQUE SCAN '
STAT #1 id=13 cnt=5 pid=8 pos=2 obj=6057 op='TABLE ACCESS BY INDEX ROWID OBJ$ '
STAT #1 id=14 cnt=9 pid=13 pos=1 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=15 cnt=2 pid=7 pos=2 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=16 cnt=5 pid=6 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=17 cnt=9 pid=16 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=18 cnt=5 pid=5 pos=2 obj=6053 op='TABLE ACCESS CLUSTER SEG$ '
STAT #1 id=19 cnt=9 pid=18 pos=1 obj=6041 op='INDEX UNIQUE SCAN '
STAT #1 id=20 cnt=9 pid=4 pos=2 obj=6055 op='TABLE ACCESS CLUSTER TS$ '
STAT #1 id=21 cnt=9 pid=20 pos=1 obj=6043 op='INDEX UNIQUE SCAN '
STAT #1 id=22 cnt=9 pid=3 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=23 cnt=9 pid=22 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=27 cnt=1 pid=2 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR '
STAT #1 id=24 cnt=5 pid=2 pos=2 obj=0 op='NESTED LOOPS '
STAT #1 id=25 cnt=85 pid=24 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO '
STAT #1 id=26 cnt=80 pid=24 pos=2 obj=6256 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #3 len=37 dep=1 uid=0 oct=3 lid=0 tim=328791893 hv=1966425544 ad='ef92eb8'
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=328791893
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=328791893
FETCH #3:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=328791893
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=6092 op='TABLE ACCESS BY USER ROWID VIEW$ '
=====================
PARSING IN CURSOR #1 len=75 dep=0 uid=376 oct=2 lid=376 tim=328791894 hv=1137854665 ad='e05d290'
insert into t select :"SYS_B_0" from all_tables where rownum <= :"SYS_B_1"
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=0,tim=328791894
EXEC #1:c=2,e=2,p=0,cr=1512,cu=6,mis=0,r=0,dep=0,og=4,tim=328791896
ERROR #1:err=1400 tim=328791896
STAT #1 id=1 cnt=3 pid=0 pos=0 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=3 pid=1 pos=1 obj=0 op='FILTER '
STAT #1 id=3 cnt=3 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=4 cnt=3 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=5 cnt=3 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=6 cnt=3 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=7 cnt=3 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=8 cnt=3 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=9 cnt=3 pid=8 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=10 cnt=945 pid=9 pos=1 obj=6057 op='TABLE ACCESS FULL OBJ$ '
STAT #1 id=11 cnt=947 pid=9 pos=2 obj=6047 op='TABLE ACCESS CLUSTER TAB$ '
STAT #1 id=12 cnt=1355 pid=11 pos=1 obj=6046 op='INDEX UNIQUE SCAN '
STAT #1 id=13 cnt=3 pid=8 pos=2 obj=6057 op='TABLE ACCESS BY INDEX ROWID OBJ$ '
STAT #1 id=14 cnt=5 pid=13 pos=1 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=15 cnt=0 pid=7 pos=2 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=16 cnt=3 pid=6 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=17 cnt=5 pid=16 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=18 cnt=3 pid=5 pos=2 obj=6053 op='TABLE ACCESS CLUSTER SEG$ '
STAT #1 id=19 cnt=5 pid=18 pos=1 obj=6041 op='INDEX UNIQUE SCAN '
STAT #1 id=20 cnt=5 pid=4 pos=2 obj=6055 op='TABLE ACCESS CLUSTER TS$ '
STAT #1 id=21 cnt=5 pid=20 pos=1 obj=6043 op='INDEX UNIQUE SCAN '
STAT #1 id=22 cnt=5 pid=3 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=23 cnt=5 pid=22 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=27 cnt=1 pid=2 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR '
STAT #1 id=24 cnt=3 pid=2 pos=2 obj=0 op='NESTED LOOPS '
STAT #1 id=25 cnt=51 pid=24 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO '
STAT #1 id=26 cnt=48 pid=24 pos=2 obj=6256 op='INDEX RANGE SCAN '
*** 2004-02-17 09:55:53.390
*** SESSION ID:(19.213) 2004-02-17 09:55:53.390
=====================
PARSING IN CURSOR #1 len=75 dep=0 uid=376 oct=2 lid=376 tim=328792939 hv=1137854665 ad='e05d290'
insert into t select :"SYS_B_0" from all_tables where rownum <= :"SYS_B_1"
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328792939
EXEC #1:c=1,e=11,p=0,cr=1259,cu=6,mis=0,r=2,dep=0,og=4,tim=328792950
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=376 oct=47 lid=376 tim=328792950 hv=1697159799 ad='db55ba0'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328792950
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=328792950
STAT #1 id=1 cnt=3 pid=0 pos=0 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='FILTER '
STAT #1 id=3 cnt=2 pid=2 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=4 cnt=2 pid=3 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=5 cnt=2 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=6 cnt=2 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=7 cnt=2 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=8 cnt=2 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #1 id=9 cnt=2 pid=8 pos=1 obj=0 op='NESTED LOOPS '
STAT #1 id=10 cnt=753 pid=9 pos=1 obj=6057 op='TABLE ACCESS FULL OBJ$ '
STAT #1 id=11 cnt=754 pid=9 pos=2 obj=6047 op='TABLE ACCESS CLUSTER TAB$ '
STAT #1 id=12 cnt=1150 pid=11 pos=1 obj=6046 op='INDEX UNIQUE SCAN '
STAT #1 id=13 cnt=2 pid=8 pos=2 obj=6057 op='TABLE ACCESS BY INDEX ROWID OBJ$ '
STAT #1 id=14 cnt=3 pid=13 pos=1 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=15 cnt=0 pid=7 pos=2 obj=6069 op='INDEX UNIQUE SCAN '
STAT #1 id=16 cnt=2 pid=6 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=17 cnt=3 pid=16 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=18 cnt=2 pid=5 pos=2 obj=6053 op='TABLE ACCESS CLUSTER SEG$ '
STAT #1 id=19 cnt=3 pid=18 pos=1 obj=6041 op='INDEX UNIQUE SCAN '
STAT #1 id=20 cnt=3 pid=4 pos=2 obj=6055 op='TABLE ACCESS CLUSTER TS$ '
STAT #1 id=21 cnt=3 pid=20 pos=1 obj=6043 op='INDEX UNIQUE SCAN '
STAT #1 id=22 cnt=3 pid=3 pos=2 obj=6060 op='TABLE ACCESS CLUSTER USER$ '
STAT #1 id=23 cnt=3 pid=22 pos=1 obj=6050 op='INDEX UNIQUE SCAN '
STAT #1 id=27 cnt=1 pid=2 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR '
STAT #1 id=24 cnt=2 pid=2 pos=2 obj=0 op='NESTED LOOPS '
STAT #1 id=25 cnt=34 pid=24 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO '
STAT #1 id=26 cnt=32 pid=24 pos=2 obj=6256 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #1 len=16 dep=0 uid=376 oct=3 lid=376 tim=328793526 hv=2959870057 ad='e930a50'
select * from t
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=328793526
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=328793526
FETCH #1:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=328793526
FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=6,dep=0,og=4,tim=328793526
*** 2004-02-17 09:57:04.038
XCTEND rlbk=0, rd_only=0
STAT #1 id=1 cnt=7 pid=0 pos=0 obj=86374 op='TABLE ACCESS FULL T '

 

what I want to know is
How to insert into a running variable
eg:-
for lvcount 0..11 loop
Insert into table1 numb'||Lvcount||'
values(Lvcount);
end loop;

I altered my session setting cursor_sharing to exact and it works as expected now. I really appreciate the time you took to look at this and the timeliness of your responses.

Hi Tom ,
I need help on a mutating table error.
My trigger updates table B when ever something is updated or inserted into table A.The update in the trigger is a correlated query that also uses columns from B to update.
Iam also pasting the query .please help.

Thanks,
M S Reddy.

create or replace trigger t_p_a
before insert or update of p_a on e_j_p
for each row
begin
update eiio f
set
(f.t_p,f.b_d ) =
(
select case when tmp.c = 0 then NVL(:new.p_a,0)
else ( tmp.s_j + NVL(:new.p_a,0) )
end as tot_pay,
case when tmp.c = 0 then NVL(:new.p_a,0)
else ( NVL(f.o_t_a,0)- NVL(f.d_a,0) - ( tmp.s_j+ NVL(:new.p_a,0) ) )
end as b_d
from ( select sum(NVL(j.p_a,0)) s_j
,count(j.ion) c
from e_j_p j
where j.ion = :new.ion ) tmp
)
where f.ss = 'C'
and f.ion = :new.ion;
end;
/

Hi Tom,

i am very much confused the way it fired the Mutating Table error,

example.

i have a before insert trigger on emp

CREATE OR REPLACE TRIGGER Trgemp
Before Insert On Emp
For Each Row
Declare
Cntrows Number;
Begin

Select Count(*) Into Cntrows
From Emp where job=:new.job;

Dbms_Output.Put_Line('count of rows : '||Cntrows);

End;

when issued a stment

insert into emp values
(7369,'SMITH','CLEARK',7902,TO_DATE('17-DEC-1980','DD-MON-RRRR'),800,NULL,2);

result :
count of rows : 0

1 row created.

the problem is when i say

insert into emp
(select 7369,'SMITH','CLEARK',7902,TO_DATE('17-DEC-1980','DD-MON-RRRR'),800,NULL,20
from dual)

result :
insert into emp
*
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRGEMP", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRGEMP'

Why such behaviour when used select stmnt to insert values

thanks for Reading the Request
Raj Ganga

I think Tom is one of the expert persons
I really appreciate him for his help
From his site
i found the answer what i am looking for
Tom, for a novice like me in Oracle i will appreciate if you answer basic things also.
You take one very deep in the subject.......

Hi Tom,

I'm trying to update nbc_task_orders(description is below) when org_id column of this table changes, I need to update task_order_status column of this table with 'Broker Terminated' for :old.nbto_id and also I need to insert a new row in the same table with task_order_status = 'Broker Active' and org_id = :new.org_id. I'm having problems in coding this trigger. 

Would you please give me some direction like how exactly it should be done.

SQL> DESC NBC_TASK_ORDERS
 Name                           Null?    Type ---------------------------- -------- -------------  NBTO_ID                         NOT NULL NUMBER(10)(PK)
 PROJECT_NBR                    NOT NULL VARCHAR2(10)
 LEASE_NBR                               VARCHAR2(10)
 CREATE_DT                      NOT NULL DATE
 CREATED_BY                     NOT NULL VARCHAR2(50)
 UPDATE_DT                      NOT NULL DATE
 UPDATED_BY                     NOT NULL VARCHAR2(50)
 ISSUED_DT                               DATE
 MODIFICATION_NBR                        NUMBER
 MODIFICATION_ISSUED_DT                  DATE
 FINAL_COTR_SUBMISSION_DT                DATE
 TASK_ORDER_NBR                 NOT NULL VARCHAR2(9)
 ORG_ID                         NOT NULL NUMBER(10)
 TASK_ORDER_TYPE                         VARCHAR2(20)
 SCHEDULED_COMPLETION_DT                 DATE
 POST_AWARD_MEETING_DT                   DATE
 COMPLETION_DT                           DATE
 NOTES                                   VARCHAR2(4000)
 TASK_ORDER_STATUS                       VARCHAR2(30)
 STATUS_DT                               DATE
 EVAL_NOTES                              VARCHAR2(4000)


Here is the sample record looks like

nbto_id     task_order_status          org_id
4            'Broker Active'            1

When I update this record (though it is update actually it should insert new record with org_id = 2)

update nbc_task_orders
set  org_id = 2
where nbto_id = 4;

Output should look like following:

nbto_id     task_order_status     org_id
4           'Broker Terminated'     1    --> old record
5           'Broker Active'         2    --> new record

Is this doable? How to achieve this. I request your help in this regard.

Thanks 
arya 

Hi Tom,

Here is what you said to my question:

"I would suggest a stored procedure to implement in a linear fashion you logic as
you layed it out. "

I did not understand clearly about your suggestion i.e. implementing this with a stored procedure in linear fashion.

You mean to say implementing this with a package variables, Row level trigger and statement level trigger to avoid mutation?

I request you to give some direction on how to do this with an example.

Thanks
arya

Hi Tom,

Before looking at your answer I have coded this with a row level trigger and statement level trigger and It is working fine.

I just now looked at your response mentioning "not to use triggers", but update to this table happens from front end(JSP) which I have no control so how can I implement this with out triggers. I'm responsible of coding this piece as I am backend developer.

Please help me understand how can I achive this without using triggers, I mean how can I capture update event which is triggered at front end.

Thanks
arya

Hi Tom,

Thanks for your response. I got it. I'll try to convince my PL about this and shall implement as you have mentioned.

Regards
Arya

Hi Tom,

In mutatation, suppose, there are two sessions (forms/sql plus), how will the system / database know the table is mutating? How will the Oracle check which session is causing this error? How we can trace this error and send a message to the later client to try after some time? If the error has occured in the client how can we stop (temporarily / permanently) that particular operation and continue further?

Thanks in advance
Raghav

Hi Tom, is there any way to indirectly reference a variable in pl/sql like we do in Oracle Forms using NAME_IN & COPY.

*****************

scott@dlfscg> set serveroutput on
scott@dlfscg> CREATE OR REPLACE PROCEDURE insert_into_dept(p_deptrow IN dept%ROWTYPE)
2 IS
3 CURSOR c1 IS SELECT * FROM user_tab_columns WHERE table_name='DEPT';
4 v_str1 VARCHAR2(4000) := 'INSERT INTO DEPT (';
5 v_str2 VARCHAR2(4000) := 'VALUES (';
6 BEGIN
7 FOR i IN c1 LOOP
8 v_str1 := v_str1||i.column_name||',';
9 IF i.data_type IN ('CHAR','VARCHAR2') THEN
10 v_str2 := v_str2||''''||'p_deptrow.'||i.column_name||''''||',';
11 ELSE
12 v_str2 := v_str2||'p_deptrow.'||i.column_name||',';
13 END IF;
14 END LOOP;
15 v_str1 := RTRIM(v_str1,',')||')';
16 v_str2 := RTRIM(v_str2,',')||')';
17 dbms_output.put_line(v_str1);
18 dbms_output.put_line(v_str2);
19 --execute immediate v_str1||' '||v_str2;
20 END;
21 /

Procedure created.

scott@dlfscg> declare
2 r dept%rowtype;
3 begin
4 r.deptno:=50;
5 r.dname:='NEW DEPT';
6 r.loc := 'new loc';
7 insert_into_dept (r);
8 end;
9 /
INSERT INTO DEPT (DEPTNO,DNAME,LOC)
VALUES (p_deptrow.DEPTNO,'p_deptrow.DNAME','p_deptrow.LOC')

PL/SQL procedure successfully completed.


*****************
I REQUIRE TO REPLACE THESE COMPOSED VARIABLE NAMES p_deptrow.DEPTNO, 'p_deptrow.DNAME','p_deptrow.LOC'
WITH THEIR VALUES e.g. 50, 'NEW DEPT', 'new loc'.
IS IT POSSIBLE

Regards,

Dear Tom,

Thanks for your responses.

In our developement process structure of a table keeps
changing so every new add or drop of column in a table
changes my insert statement for that table.

So, what i was trying to do is that creating a "insert
procedure" for each table with a parameter of same
rowtype and forget to take care of insert statements
forever.

thanks and Regards,

hi tom,
i'm very thankfull for your solution, i already managed the "Mutating Table"-Problem and run into the "max recursion reached" error - with your solution, every thing works fine :-)

I have two tables where A is B´s "father" on delete cascade.
In my B_d trigger(delete), I check some status in table A(a simple select in some columns). If I delete some lines in table A, I´m now receiving ORA-04091: table A is mutating, trigger/function may not see it.

Is there how can avoid this??

"I hate triggers... so many side effects that people don't think about."

I have seen this statement at other sites as well.
I am curious how you would implement functionalities
such as "before insert on a row" or "after delete on a row" without triggers. My hunch is that you advocate
encapsulating all DMLs in a procedure. So insert into
emp would be ultimately an API called insert_emp and so
on (Note I am not refering to TAPI - these procedures
would in turn be invoked by real Transaction-oriented
procedures.)
Is that what you mean or is there more to it?

Many thanx!

I was wondering about why you hate triggers. I also
think transactional APIs are great. But my question was
how you can replace the functionality of triggers? In that
sense would your answer be the following?
" by enclosing each DML
into a procedure - that way if something needs to be
done before insert, I can do that in the procedure
directly *Without* creating a before-insert trigger."
Or is there something else that I am missing? Bottom line
question, if you hate triggers how would you replace
their functionality?

I agree and I think we are saying the same thing.
What I am saying is:
Let us say we have a functionality of hire_emp.
the way we can implement is

hire_emp
(
insert_emp
)

That way, if something needs to be done before
or after inserting into emp, we can do it in the
insert_emp method. Also, in many cases, there may
be multiple places where you may be calling the
insert api - so makes sense to make it an API.

Since it is an API, we avoid the "hidden" affects
of triggers. Need to do something "after" insert,
just do it inside insert_emp.

Please confirm if this is what you mean...

Sir,
May i know why we can't use an after trigger for updating the row after inserting .Why that shows a mutating error?

Hi Tom,

I've run into a problem using your workaround, I have the package create, before insert/update, after insert/update (row) and after insert/update triggers setup nearly identical to yours (different logic in the loop).

The trigger does not seem to be entering the loop... trying to insert the rowid from the array into a type rowid field in a debug table causes a no data found error, debug inside the loop produces nothing. The trigger logic works fine, in a single user test environment, running the trigger as an autonomous block produces the desired results.

Any suggestions?

Main trigger posted below:

CREATE OR REPLACE TRIGGER "UOW_EMPLOYEE_AFTER_INS_UPD"
AFTER INSERT OR UPDATE OF "DATE_OF_BIRTH",
"EMPLOYEE#",
"FIRST_NAME",
"GENDER",
"PREFERRED_NAME",
"SECOND_NAME",
"SURNAME",
"TAXNO",
"THIRD_NAME",
"TITLE",
"USERID"
ON "EMPLOYEE"
DECLARE
/* Formatted on 2005/06/13 15:45 (Formatter Plus v4.8.5) */
-- Version 1.0
-- this trigger processes the new rows. We simply loop over the newRows
-- array processing each newly inserted/modified row in turn.

v_sqlerrm VARCHAR2 (2000);
l_upd_audit_id uow_auth_employee_updates.audit_id%TYPE;
l_del_audit_id uow_auth_employee_updates_del.audit_id%TYPE;
l_uow_auth_employee uow_auth_employee_updates%ROWTYPE;
l_sub_emp_status substantive.emp_status%TYPE;
l_sub_occup_term_date substantive.occup_term_date%TYPE;
l_uow_exclude_group codes.code%TYPE;
l_create BOOLEAN;
l_employee# employee.employee#%TYPE;
l_userid employee.userid%TYPE;
l_date_of_birth employee.date_of_birth%TYPE;
l_title employee.title%TYPE;
l_surname employee.surname%TYPE;
l_first_name employee.first_name%TYPE;
l_second_name employee.second_name%TYPE;
l_third_name employee.third_name%TYPE;
l_preferred_name employee.preferred_name%TYPE;
l_gender employee.gender%TYPE;
l_taxno employee.taxno%TYPE;


CURSOR cu_uow_auth_employee(p_employee# VARCHAR2)
IS
SELECT *
FROM uow_auth_employee_updates
WHERE uow_auth_employee_updates.employee_no = p_employee#;

CURSOR get_substantive(p_employee# VARCHAR2)
IS
SELECT emp_status, occup_term_date
FROM substantive s1
WHERE employee# = p_employee#
AND commence_date =
(SELECT MAX (commence_date)
FROM substantive s2
WHERE s2.employee# = s1.employee#
AND SYSDATE < NVL (s2.occup_term_date, '31-DEC-2049'));

CURSOR cu_check_group (l_code VARCHAR2)
IS
SELECT code
FROM codes
WHERE kind = 'UOW_EXCLUDE_AUTH' AND code = l_code;

CURSOR cu_get_employee_data(p_rowid ROWID)
IS
SELECT employee#, userid, date_of_birth,
title, surname, first_name, second_name,
third_name, preferred_name, gender, taxno
FROM employee
WHERE rowid = p_rowid;

BEGIN
FOR a IN 1 .. uow_employee_state.newRows.count
LOOP
OPEN cu_get_employee_data(uow_employee_state.newRows(a));
FETCH cu_get_employee_data INTO l_employee#, l_userid, l_date_of_birth,
l_title, l_surname, l_first_name, l_second_name,
l_third_name, l_preferred_name, l_gender, l_taxno;
CLOSE cu_get_employee_data;

OPEN cu_uow_auth_employee(l_employee#);
FETCH cu_uow_auth_employee INTO l_uow_auth_employee;

/* case 1 - for some reason the uow_auth_employee_updates
record does not exist - so create it */
IF cu_uow_auth_employee%NOTFOUND THEN
/* First determine if the substantive record exists, if it does
then we create the complete record, otherwise we create it CON_PEND
and wait until the substantive record gets updated before we allow UO
to process it. */
OPEN get_substantive(l_employee#);
FETCH get_substantive INTO l_sub_emp_status, l_sub_occup_term_date;

IF get_substantive%FOUND THEN
OPEN cu_check_group (l_sub_emp_status);
FETCH cu_check_group INTO l_uow_exclude_group;

IF cu_check_group%NOTFOUND THEN
-- Group is ok
l_create := TRUE;
END IF;

CLOSE cu_check_group;

END IF;

CLOSE get_substantive;

/* If the Group is ok, above, then we can create the record */
IF l_create THEN
SELECT audit_id.NEXTVAL
INTO l_upd_audit_id
FROM DUAL;

INSERT INTO uow_auth_employee_updates
(employee_no, auth_action_date, auth_action_status,
update_user, date_of_birth, title,
surname, first_name, second_name,
third_name, known_name, gender,
uow_account_id, email_address, ird_number, tp,
tp_time, tp_type, audit_date,
audit_id, userid
)
VALUES (l_employee#, SYSDATE, 'UOW_PEND',
l_userid, l_date_of_birth, l_title,
l_surname, l_first_name, l_second_name,
l_third_name, l_preferred_name, l_gender,
NULL, NULL, l_taxno, TRUNC (SYSDATE),
TO_CHAR (SYSDATE, 'HH24MISS'), 'A', SYSDATE,
l_upd_audit_id, USER
);
END IF;
ELSE
/* case 2 - uow_auth_employee_updates exists so create _del entry and update */
UPDATE uow_auth_employee_updates
SET gender = l_gender,
known_name = l_preferred_name,
third_name = l_third_name,
second_name = l_second_name,
first_name = l_first_name,
surname = l_surname,
title = l_title,
date_of_birth = l_date_of_birth,
update_user = l_userid,
auth_action_status = 'UOW_PEND',
auth_action_date = SYSDATE,
ird_number = l_taxno,
tp = TRUNC (SYSDATE),
tp_time = TO_CHAR (SYSDATE, 'HH24MISS'),
tp_type = 'C',
userid = l_userid
WHERE uow_auth_employee_updates.employee_no = l_employee#;
END IF;

CLOSE cu_uow_auth_employee;

END LOOP;

EXCEPTION
WHEN others THEN
v_sqlerrm := SQLERRM;
raise_application_error (-20202,
v_sqlerrm
|| ' Error in trigger uow_employee_upd. '
);
END;
/

I'm not using an autonomous transaction?

This trigger is used to give access to our web kiosk product. It reads the table we've just updated in the first cursor (cu_get_employee_data), then the rest of it is reading other tables and inserting into the authorisation table. No need for an autonomous transaction.

Here's a hopefully more complete (and simplified) example:

create table employee (
employee# NUMBER(8),
first_name VARCHAR2(20));

create table web_access (
employee# NUMBER(8),
first_name VARCHAR2(20),
status VARCHAR2(10));

create or replace package uow_employee_state
as
type ridArray is table of rowid index by binary_integer;

newRows ridArray;
empty ridArray;
end;
/

create or replace trigger uow_employee_before_ins_upd
before insert or update on employee
begin
uow_employee_state.newRows := uow_employee_state.empty;
end;
/

create or replace trigger uow_employee_after_row
after insert or update of EMPLOYEE#,
FIRST_NAME
on employee for each row
begin
uow_employee_state.newRows( uow_employee_state.newRows.count+1 ) := :new.rowid;
end;
/

CREATE OR REPLACE TRIGGER "UOW_EMPLOYEE_AFTER_INS_UPD"
AFTER INSERT OR UPDATE OF EMPLOYEE#,
FIRST_NAME
ON EMPLOYEE
DECLARE
l_employee# employee.employee#%TYPE;
l_first_name employee.first_name%TYPE;
l_second_name employee.second_name%TYPE;
l_uow_auth_employee web_access%ROWTYPE;


CURSOR cu_uow_auth_employee(p_employee# VARCHAR2)
IS
SELECT *
FROM web_access w
WHERE w.employee# = p_employee#;

CURSOR cu_get_employee_data(p_rowid ROWID)
IS
SELECT employee#, first_name
FROM employee
WHERE rowid = p_rowid;

BEGIN
FOR a IN 1 .. uow_employee_state.newRows.count
LOOP
OPEN cu_get_employee_data(uow_employee_state.newRows(a));
FETCH cu_get_employee_data INTO l_employee#, l_first_name;
CLOSE cu_get_employee_data;

OPEN cu_uow_auth_employee(l_employee#);
FETCH cu_uow_auth_employee INTO l_uow_auth_employee;

IF cu_uow_auth_employee%NOTFOUND THEN
SELECT audit_id.NEXTVAL
INTO l_upd_audit_id
FROM DUAL;

INSERT INTO web_access (employee_no,
status,
first_name)
VALUES (l_employee#,
'UOW_PEND',
l_first_name);
END IF;
ELSE
UPDATE web_access
SET first_name = l_first_name,
status = 'UOW_PEND',
WHERE web_access.employee = l_employee#;
END IF;

CLOSE cu_uow_auth_employee;

END LOOP;

EXCEPTION
WHEN others THEN
v_sqlerrm := SQLERRM;
raise_application_error (-20202,
v_sqlerrm
|| ' Error in trigger uow_employee_upd. '
);
END;
/

/

Sorry, my mistake... I meant to say anonymous block.

Just got the client to send me back his versions of the triggers... turns out he overwrote the old versions (which were all "for each row"), just replacing the text, instead of removing the old ones and installing the new ones.

Got him to do it properly and it all works now.

Sorry for wasting your time :(

I have trigger which will be fired AFTER INSERT ON test
FOR EACH ROW WHEN (NEW.test_NUMBER >0), I would like to update other fields in this table test based on some condition. I am giving
if j=0 then
:NEW.field1:=variable1;
elsif j=1 then
:NEW.field2:=variable2;
else
:NEW.field3:=variable3;
end if;
j:=j+1;

But, while compiling, I am getting ORA-04084: Cannot Change New values for this trigger type".
Please tell me, how to get over this issue.

i have reviewed.but i have a problem.ihave two tables A,B.
after delete on a the status column of b is updated autometically.Both A B has matching field as temp_voucher_no,voucher_no,voucher_date.
i have to write a trigger for that can u please suggest

Hi Tom,
Facing a mutating trigger problem and not able to resolve it .Please suggest an alternative solution.
CREATE OR REPLACE TRIGGER TESTTRG
BEFORE UPDATE OF COL2
ON TEST
FOR EACH ROW

DECLARE
x NUMBER;
y NUMBER;
BEGIN
x := 0;
SELECT COUNT ( * ) INTO x FROM TEST WHERE COL1 = :OLD.COL1 AND COL2 IS NOT NULL;
SELECT COUNT ( * )INTO y FROM TEST WHERE COL1 = :OLD.COL1 AND COL2 IS NULL;
IF ( ( :NEW.COL2 IS NULL AND 0 < x) OR ( :NEW.COL2 IS NOT NULL AND 0 < y ) ) THEN
RAISE_APPLICATION_ERROR(-20037, 'Both null and non-null Product Types not permitted for the same ORDER');
END IF;
END;

Tom, you showed us how to avoid mutating table problem on 

http://asktom.oracle.com/~tkyte/Mutate/index.html

I tried to reproduce the mutating table problem using the example without using your solution and expected that I will get the mutating table eror. However, the update went fine! I'm not sure why I didn't get an error.

SQL> CREATE TABLE parent (
  2  theKey    INT PRIMARY KEY,
  3  status    VARCHAR2(1),
  4  effDate   DATE
  5  )
  6  /

Table created.

SQL> 
SQL> CREATE TABLE log_table (
  2  theKey    INT references parent(theKey),
  3  status    VARCHAR2(1),
  4  effDate   DATE
  5  )
  6  /

Table created.

SQL> 
SQL> CREATE OR REPLACE TRIGGER parent_trig
  2  AFTER  INSERT OR UPDATE OF status ON parent
  3  FOR EACH ROW
  4  BEGIN
  5     IF (INSERTING) THEN
  6        INSERT INTO log_table VALUES (:new.theKey, :new.status, :new.effDate);
  7  
  8     ELSIF (UPDATING) THEN
  9        IF (:new.status <> :old.status) THEN
 10           INSERT INTO log_table VALUES (:new.theKey, :new.status, :new.effDate);
 11        END IF;
 12     END IF;
 13  END;
 14  /

Trigger created.

SQL> insert into parent values ( 1, 'A', sysdate-5 );

1 row created.

SQL> insert into parent values ( 2, 'B', sysdate-4 );

1 row created.

SQL> insert into parent values ( 3, 'C', sysdate-3 );

1 row created.

SQL> insert into parent select theKey+6, status, effDate+1 from parent;

3 rows created.

SQL> 
SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate;

6 rows updated.

SQL> commit;

Commit complete.

SQL> select * from log_table;

    THEKEY S EFFDATE
---------- - ---------
         1 A 22-JAN-06
         2 B 23-JAN-06
         3 C 24-JAN-06
         7 A 23-JAN-06
         8 B 24-JAN-06
         9 C 25-JAN-06
         1 B 27-JAN-06
         2 C 27-JAN-06
         3 D 27-JAN-06
         7 B 27-JAN-06
         8 C 27-JAN-06
         9 D 27-JAN-06

12 rows selected.

 

Tom:

I have a row level before insert trigger on a table which calls a stored procedure to do some other complex logic.
In between the stored procedure access the data just inserted into the table as:

say x is (id number, desc_add varchar2(100), some_other_id number...other columns);

insert into table x values (1,'AAAA',10, ..... );
insert into table x values (1,'AAAA', null,....);

for id=1 i need to get the value from first insert statement for a particular column to be replace in the second insert statement. What is the best way for me to achieve this. (The way i am doing currently is to insert the rows into another table and do a select on this table .... and then delete the rows after my transaction is done....)

I know there are some serious design flaws, but this is the legacy code which got converted so we have to leave with this till everything is re-written.


Thanks in Advance,

Tom:

As an addenda for my previous post:

The insert statements will not happen immediately....It can happen much later in the transaction (ie when other transactions take place )

Thanks

Tom:

Well no...that is just the example...The logic is as follows


1. Select (concerned column) from the <x, table2 other database tables) where x.id=1

2. This also includes the rows from the present transaction set which are not yet committed.

Thanks,

Here is an example

Table X:
ID , DESC , OTHER_ID, ..........

1 'AAAA' , 10 , .......... > not yet commited
1 'AAAA', null, ----------->Not yet commited


Table Y:

OTHER_ID DESC

10, 'PPP' ---------->not yet commited.

so for the first record the join condition will satisfy for

" X.other_id = Y.other_id and X.id = 1)

for the second statement : It should now depend on the first statement as its other id is null. But there might be other 100 insert statements with different ID's...before we hit 2nd insert statement again for ID=1.

Thanks,

Thanks,

Tom:

The select statement is the stored procedure that the trigger calls.

I wish I could change...but being the legacy code which got converted from a database which supported dirty reads....it will impact so many things. So we have to wait till everything gets re-written in Oracle. So I am looking for a temporary fix.

I created a table (permanent) and am inserting the data into it also along with table x and then querying it. But the problem is in deleting the data after my transaction is done.

Thanks,

Tom:

sorry about the confusion. Let me clarify...


create table table1 (id number, desc_10 varchar2(100), other_id number);

create table table2 (other_id number, other_desc varchar2(100));


1. A trigger exists on table1 called table1_trig which is before insert (row level)

begin

sample_sp (:new.id, ...., :new.desc,...);

end;

2. This trigger calls a stored procedure called sample_sp(id in, ...)




Contents of sample_sp(v_id in ......):

begin

select other_desc into v_otherdesc from
table1, table2
where table1.id = v_id
and table1.other_id = table2.other_id;

if v_otherdesc is null then raise exception.


< other complex logic >

exception when no_data_found then

< serious error >

end;

3. Table2 has already commited data for other_id = 10.

Here is the transaction:

begin

insert into table1 values (1,'AAAA', 10, ...);
---
other inserts
insert into table1 values (1,'AAAA',null.....);

The second insert statement should get other_id from the first one....

Please let me know...I will try to send the complete case study if this does not explain my point. My apologies for not specifying it correctly.

Thanks in Advance,

Tom:

This is transaction by transaction.

Thanks

TableA has a trigger which "does some stuff" on INSERT.

If I insert into TableA from Values, I get no mutating table.

Now, Create TableB as Select * from TableA where rownum = 1. I have a completely new and syntactically unrelated table -- Just plain'ol TableB.

INSERT INTO TableA SELECT * FROM TableB gives me the mutating table error. Why? This would make sense (to me) if I were getting my values from TableA or a table affected by "does some stuff" But B is brand new, it's just a convenience for holding my test record.

Specifically the error says the problem occurred at the assignment line:

SELECT DEAL_SEQ.NEXTVAL
INTO PKID
FROM DUAL;

:NEW.DEAL_ID := PKID;

I hate it when stuff like this just rears up. :-\

My Insert from Select had a predicate which would produce zero or one row --> WHERE Rownum = 1. This is what threw me. How could ONE ROW or ONE LIST OF VALUES behave so differently.

I assume that Oracle doesn't bother to do the query before throwing the error... it merely sees the select and fails. It seems a bit misleading to throw an error when there isn't one.

Shouldn't this a case of Caveat Developer? I should be able to insert from select if I promise to only return 1 or fewer rows.

FAILS:
------
INSERT INTO table_with_trigger
SELECT FROM data_table
WHERE rownum = 1


SUCCEEDS:
---------
SELECT INTO variable_list FROM data_table WHERE rownum = 1

INSERT INTO table_with_trigger VALUES (variable_list)



I understand that a Select leaves the possibility that it ** may ** contain more than one row but so does SELECT INTO. That throws an error for <> 1 but it does succeed if possible.

(Sorry about the semi-new thread here - maybe you've covered this already)

Hi Tom - I agree with your dislike of triggers - to often I've had to diagnose a some "weird" condition in a database system only to find there is a trigger at work in the background doing something.

But when, if ever do you think triggers are acceptable? I like them for "auto incrementing" a key value from a sequence, and I see their use for various auditing functions, but when else do you think they are OK?

Tom, I need to implement a business rule like “do not allow update a row with state A if exists three rows with state A” (I thing in a trigger), but I get a mutating error with:
create table T1
( year number(4),
version number(1),
state varchar2(1),
constraint PK_T1 primary key(year, version)
);
create or replace function SF_STATE_A return pls_integer is
li_return pls_integer;
begin
select nvl(max(year), to_number(to_char(sysdate, 'YYYY'))) into li_return from t1;
select count(*) into li_return from t1 where year = li_return and state = 'A';
return li_return;
end SF_STATE_A;
/
create or replace trigger TRG_BUR_T1 before update on T1
referencing new as new old as old for each row
declare
begin
if :new.state = 'A' and sf_state_a = 3 then
raise_application_error(-20001, 'Update');
end if;
end TRG_BIUDR_T1;
/
insert into bnfpresupuesto.T1(year, version, state) values(2007, 1, 'A');
insert into bnfpresupuesto.T1(year, version, state) values(2007, 2, 'A');
insert into bnfpresupuesto.T1(year, version, state) values(2007, 3, 'A');
insert into bnfpresupuesto.T1(year, version, state) values(2007, 4, 'I');
update bnfpresupuesto.t1 set state= 'A' where year=2007 and version=4;

???

Ok, that table is not for a multi-user environment and it is the parent table, too. but the developer team, just have tell me that the business rules are changing, so thanks for your advise, I really appreciate your help.

Tom,

I used the workaround you gave us to avoid the notorious mutating table. Though it works, the problem arises when two users trying to update the same row. The deadlock won't resolve until any one of us commit the transaction. To avoid this I used “commit” inside the trigger or inside the procedure being called by a trigger. Neither of them seems to work as it pops out an error "ORA-04092: cannot COMMIT in a trigger". Is it not possible to do a commit in this situation? I saw another post where you used “commit” inside a trigger (AFTER LOGON trigger).

We appreciate your help always.

So, I write an upload procedure to do an import from a CSV. I get a mutating error. I go and look at the insert trigger, and see this code:
<pre>
if nvl(:new.adj_no,0) = 0 then
select nvl(max(adj_no),100) + 1 into :new.adj_no from nkw.cust_cr_adj;
end if;
</pre>

The reason this code was working is because an insert had been done only 1 row at a time. When doing multiple inserts at a time, the table started to mutate. I had to create the sequence, and fix the insert trigger to populate the PK from the sequence.

Just wondering, if anyone else has issues like this, when they try to fix databases that they didn't build.

ARGH!!!!!!!!!!!!!!!!!!!

-Scott

i know that using autonomous triggers for this problem has complications ...

What about using a autonomous sub-procedure inside the trigger? Would this work fine ?


CREATE OR REPLACE TRIGGER XXX
BEFORE INSERT OR UPDATE OR DELETE
ON XXX REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

PROCEDURE aut_trans(p1 number, p2 number)
IS
Pragma Autonomous_transaction;
v_test number;
BEGIN
SELECT 1 INTO v_test
FROM XXX
<WHERE CLAUSE>;
END;

BEGIN
aut_trans(p1,p2);
END;

Hi Tom,

We have a system where we deliver market researched data to customers on daily basis.
Some times we might have to delete a particular record (data) because the market data becomes irrelevant.
Few our customers suggested that the data should be marked for deletion and should be delted periodically rather than just delete it.
We came up with a two columns tobedeleted (yes/null) and tobedeltedflagdt (sysdate). The idea is to delete flagged data after 6 months of flagged date.

Tables are structured something like this (for example);

PARENT_TABLE->CHILD_TABLE1->CHILD_TABLE2->CHILD_TABLE3->CHILD_TABLE4
| |
-->CHILD_TABLE21 -->CHILD_TABLE41
| |
-->CHILD_TABLE22 -->CHILD_TABLE42
| |
-->CHILD_TABLE221 -->CHILD_TABLE421

We have about 70 tables with these kind of nested relations.

The updates should propagate from parent to child when tobedeleted is updated.

I tried to implement this with database triggers but implementation seems very messy.
1) The tables are too big and response time on UI is very long as we are doing lot of backend processing for updating a single column.
2) Data seems to become inconsistent with multiuser enviroment(OLTP). Not sure why this is happening, I am still investigating.
3) in our system we dont actually DELETE/UPDATE a record, we "enddate" the record and create new record with changed attributes (similar to slow changing dimensions). Hence, i have to query "previous" state of the record and do the processing only if value of tobedeleted is changed. This brings me to deal with mutating table issue. I have to preserve the state of the table in global temp table or something and do the processing
4) not sure what other issues would come up with the triggers
5) maintaining these many triggers on these many tables would be heck of a job. Something goes wrong, debugging would be a mess.

Is there a better way of accomplishing this? Should this be implemented in application logic?

I hope i gave enough information for the issue on hand. Please let me know if you need more info.

Thanks.

Tom,

This is not redundant data. They are something like master detail tables. If master gets marked for deletion, respective children should also be marked for deletion.

hi,
tom your website is very useful to me,as a fresher i am learning many things from your website.

i have created table.it contains column last_modified.
and i have created a trigger(before insert or update for each row on that table.)when ever we update or insert values into that table, that last_modified column shoud take sysdate as value.
while i am doing this iam facing mutating error problem.

create trigger ....
begin
   :new.last_modified := sysdate;
end;
/

Hi,

consider,

TABLE_A

col1 col2 col3
---- ----- -----
xa 8 25/06/2010
xa 3 25/08/2013 -----> new row


TABLE_B

col1 col2 col3 col4
---- ----- ----- -----
xa 8 description 25/06/2010


Whenever i insert a new row in TABLE A, and if col2 new value is 3 and old value is 8, i need to
set my TABLE B col3 value to null.

I need to handle this through a trigger on TABLE A, which results in mutating table error. find my trigger below.

CREATE OR REPLACE TRIGGER user.AIR_TRG
AFTER INSERT ON user.AIR_TRG FOR EACH ROW
DECLARE
...
...
begin
...
...
FOR i IN (select nvl(lag(col2,1) over ( order by start_date),0) prev_val
from TABLE A where col1 = :new.col1)
LOOP

IF i.col2 = 3 AND i.prev_val = 8
THEN
UPDATE TABLE_B
set col3 = NULL
where col1 = :NEW.col1;
commit;
END IF;
END LOOP;
END;

end;

how to hanlde this?

Thanks,
Ann

create or replace view v
as
select * from 
(
select col1, col2, lead(col2) over (partition by col1 order by col3) next_col2, col3
from t
)
where col2 = 8 and next_col2 = 3;