Orcle trigger

[akutty]

Hi All,

The trigger below gets compiled succesfully, however it doesnt work as expected, what we want is when count not = 0 then it shd raise the error. somehow its not working
please help
regards
Anil

create or replace trigger Tr_CheckSRID
before insert or update of SR_ID on DTS_T_VIC_VOLUME
for each row
declare
temp Varchar(2);
cursor c1 is select count(*) Countsr from DTS_T_VIC_VOLUME WHERE SR_ID not in ( select distinct SR_ID from T_SR_Salesperson_Details) ;
begin

for temp in c1
loop

if temp.Countsr <>0 then
RAISE_APPLICATION_ERROR (-20107,'SR ID doesnt exists in column T_SR_Salesperson_Details');
end if;
end loop;
Exception
when no_data_found then
RAISE_APPLICATION_ERROR (-20100,'No data found on table T_SR_Salesperson_Details');
end Tr_CheckSRID ;

 

[SantaMufasa]

Anil,

You said,

(The trigger) doesnt work as expected

...What does the trigger do? You raise the error, ('-20107'), but have no exception handler for that exception, therefore you probably see something like, "Unhandled user-defined exception...".

(I'm sorry if I cannot respond quickly beyond this post...I must go to a Utah Oracle Users Group conference presently. But I'm sure one of the other Tek-Tipsters can respond to your reply.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

How about

create or replace trigger Tr_CheckSRID 
  before insert or update of SR_ID on DTS_T_VIC_VOLUME
  for each row
declare
temp number;
cursor c1 is select 1 from T_SR_Salesperson_Details 
where  SR_ID=:new.SR_ID;
begin

open c1;
fetch c1 into temp;

if c1%notfound then
  close c1;

  RAISE_APPLICATION_ERROR (-20107,'SR ID doesnt exists in column T_SR_Salesperson_Details');
else
  close c1;
end if;

end Tr_CheckSRID  ;

I'd better not to comment/correct your code

Another idea is if possible to create foreign key.

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

You have a "MUTATING TABLE" Error due to this cursor:

cursor c1 is select count(*) Countsr from DTS_T_VIC_VOLUME

You cannot issue DML on the table which fired the trigger.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[engineer2100]

Anil,

change to something like this

create or replace trigger Tr_CheckSRID 
  before insert or update of SR_ID on DTS_T_VIC_VOLUME
  for each row
declare
temp number;
cursor c1 is select count(1) Countsr from T_SR_Salesperson_Details where SR_ID = nvl(:new.DTS_T_VIC_VOLUME.SR_ID, :old.DTS_T_VIC_VOLUME.SR_ID);
begin
temp := 0;
open c1;
fetch c1.countr into temp;
if temp <>0 then
RAISE_APPLICATION_ERROR (-20107,'SR ID doesnt exists in column T_SR_Salesperson_Details');
end if;
Exception
when no_data_found then
RAISE_APPLICATION_ERROR (-20100,'No data found on table T_SR_Salesperson_Details');
end Tr_CheckSRID  ;

This way you can avoid the possible mutating Error. And there is really no need of the Loop.

Let me know if this helps.

-Engi

 

[engineer2100]

Sorry Sem... I didnot see your code and posted mine...

 

[akutty]

Thanks a lot to all . working fine now