CODEWITHSUNDEEP
oracle-databaseplsql
saniya mapari
saniya mapari

Reputation: 71

ora-01403 no data found error,ora-04088 error during execution of trigger

Hi I have this trigger

create or replace TRIGGER trg_cust
after insert or update on nbty_customer
referencing old as old new as new
for each row
declare
pragma autonomous_transaction;
V_REQ VARCHAR2(10);
begin 

 IF(:NEW.cust_sal<1000)
        THEN
        select :NEW.cust_sal+:OLD.cust_sal INTO V_REQ FROM nbty_customer
        where cust_id=:old.cust_id;

    ELSE
    SELECT :NEW.cust_sal-:OLD.cust_sal INTO V_REQ FROM nbty_customer where cust_id=:old.cust_id;
    end if;

merge into nbty_cache_cust 
    using
       (select distinct :new.cust_id cust_id, :new.cust_name cust_name,V_REQ V_REQ
       from nbty_customer) b
       on (cust_nbty_id = b.cust_id)
        when matched then
       update set cust_nbty_name = b.cust_name, cust_nbty_sal = b.V_REQ 
       when not matched then
      insert (cust_nbty_id, cust_nbty_name, cust_nbty_sal)
      values (b.cust_id, b.cust_name, b.V_REQ);
      commit;
      end;

Which compiles properly but when an insertion is done on the table nbty_customer like for example

insert into nbty_customer values('2','abc','200')

It throws ora-01403 no data found error,ora-04088 error during execution of trigger Kindly help, I am not able to figure out what the issue is?

Upvotes: 1

Views: 4793

Answers (3)

saniya mapari
saniya mapari

Reputation: 71

The problem was due to a select into clause which returned a 'no data found' so I removed the select into and directly used a bind variable instead:

IF(:new.cust_sal<1000)
then
  v_req:=nvl(:new.cust_sal,0)+nvl(:old.cust_sal,0);
else
  v_req:=nvl(:new.cust_sal,0)-nvl(:old.cust_sal,0);
end if;

This solved the problem.

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30845

In your trigger body, you're trying to select the data of your new row:

 SELECT :NEW.cust_sal-:OLD.cust_sal INTO V_REQ FROM nbty_customer where cust_id=:old.cust_id;

But you declared your trigger body as PRAGMA AUTONOMOUS_TRANSACTION. Therefore, your trigger does not see the changes that triggered the trigger execution in the first place.

Using AUTONOMOUS_TRANSACTION in triggers is never a good idea; see Tom Kyte on Triggers for a detailed explanation on why you want to avoid this.

If you need to cache your data for performance reasons, I'd recommend using a Materialized View instead. You can set it to REFRESH ON COMMIT if needed:

create materialized view mv_cache
refresh on commit 
as 
select distinct cust_id cust_id, cust_name cust_name, V_REQ 
from nbty_customer

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 157136

I just commented, and then I noticed this:

select :NEW.cust_sal+:OLD.cust_sal INTO V_REQ FROM nbty_customer
    where cust_id=:old.cust_id;

On insert, this select will fail since there is no :old.cust_id. Use :new.cust_id instead. (also, :old.cust_sal will be null too)

Upvotes: 1

Related Questions