Why doesn't this trigger work properly?

0
votes

From what I have read about triggers and how they work, I thought that this trigger would insert data into the relations related to my table phonenumber after I insert values into it. I'm using dbms_random to create a random 5 digit usageID not already in the usage table (or atleast that's what I had thought it would do).

create or replace TRIGGER addPhoneLine
AFTER INSERT ON phoneNumber
REFERENCING NEW AS NEW
  FOR EACH ROW

DECLARE
    primNum varchar(12);
    acctNum numeric(5);
    NEWusageID_new varchar(5);

BEGIN

    if :new.primaryNumber is not NULL then
        select acctID, primaryNumber into acctNum, primNum
        from account A
        where A.primaryNumber = :new.primaryNumber;

        select to_char(round(dbms_random.value(10000, 99999),0)) into 
NEWusageID_new from dual
        minus
        select usageID from usage;

        INSERT INTO acct_num VALUES
        (acctNum, primNum, :new.phonenumber);

        INSERT INTO phone_usage VALUES
        (NEWusageID_new, :new.phonenumber);
    end if;

END;

But it throws the following errors when I attempt to insert into the phoneNumber table:

ORA-01403: no data found

ORA-06512: at "ADDPHONELINE", line 9

ORA-04088: error during execution of trigger 'ADDPHONELINE'

The relevant tables were created as follows:

create table phoneNumber(phoneNumber varchar(12) PRIMARY KEY, primaryNumber varchar(12));

create table acct_num(acctID numeric(5) references ACCOUNT, primaryNumber varchar(12) references ACCOUNT,  phoneNumber varchar(12) references phoneNumber);

create table phone_usage(usageID varchar(5) references USAGE, phoneNumber varchar(12) references PHONENUMBER)
3
sqloracleplsqltriggersoracle-sqldeveloper

3 Answers

1
votes

Your trigger is based on an insert on table phoneNumber and the error "No Data Found" is thrown when a SELECT INTO is used and it doesnt find any information to insert.

So the problem must be this statement. 

select acctID, primaryNumber into acctNum, primNum
from account A
where A.primaryNumber = :new.primaryNumber;

Are you certain that the :new.primaryNumber exists in the account table when this trigger is active?

Maybe you are only populating the account table after this insert is complete?

0
votes

The below query is returning no rows, as per error description you posted. Either your :new.primaryNumber is incorrect or there is no matching record in the account table.

Begin 
   select acctID, primaryNumber into acctNum, primNum
    from account A
    where A.primaryNumber = :new.primaryNumber;
exception when others then
  insert your favorite logging method here logging out your :new.primaryNumber
end;
0
votes

I show you here what will happen when dbms_random gives a value (NEWusageID_new), which exists in the usage:

DECLARE
   i   NUMBER;
BEGIN
   SELECT 15 INTO i FROM DUAL
   MINUS
   SELECT 15 FROM DUAL;
END;

ORA-01403: no data found
ORA-06512: in line 4

Use a sequence instead.