CODEWITHSUNDEEP
sqloracle-databasetriggers
c300g97
c300g97

Reputation: 21

Oracle Trigger error "ORA-00942: table or view does not exist"

this is my trigger :

    create trigger tampone_trigger
after insert on tamponi.numerotelpaziente
for each row
begin
    IF ( :new.numerotelpaziente not in ( 
    select numtel 
    from users))
    then 
    insert into spaiati values (new.numtel);
    end if;
end;

The table "Tamponi" does exists, and "numerotelpaziente" is one of the columns... Table "USERS" also exists, and "numtel" is one of its columns... Why on earth is giving me that one error? The trigger is supposed to look for this new cellPhone number inserted into "Tamponi" and check if this number exists in "Users", if not it has to be add to the separated table "spaiati" , where there is a column for it.. It is perfectly connected to my personal database (i'm running my JAVAfx application onto it, and it works fine, i just need to create some triggers).

    Report error -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

If i use "on Tamponi" instead of giving the column aswell, the error becomes this one :

Report error -
ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 -  "NEW or OLD references not allowed in table level triggers"
*Cause:    The trigger is accessing "new" or "old" values in a table trigger.
*Action:   Remove any new or old references.

if i use "on tamponi" the error is now this :

2/5       PL/SQL: Statement ignored
2/40      PLS-00405: subquery not allowed in this context
Errori: controllare il log del compilatore

Upvotes: 0

Views: 670

Answers (3)

EJ Egyed
EJ Egyed

Reputation: 6094

Since this code will be in a trigger, you will want it to be as efficient as possible since it might be run very often. The code below should do what you are hoping to achieve with minimal context switching.

CREATE TRIGGER tampone_trigger
    AFTER INSERT
    ON tamponi
    FOR EACH ROW
BEGIN
    INSERT INTO spaiati
        SELECT :new.numerotelpaziente
          FROM DUAL
         WHERE NOT EXISTS
                   (SELECT 1
                      FROM users u
                     WHERE u.numtel = :new.numerotelpaziente);
END;
/

Upvotes: 2

EasterBunnyBugSmasher
EasterBunnyBugSmasher

Reputation: 1582

I don't have a DB client available currently, but this should be close to what you want:

create trigger tampone_trigger
  after insert on tamponi for each row
declare
    v_exists number;
begin
    select count(*) into v_exists from users u where u.numtel = :new.numerotelpaziente;
    if (v_exists = 0) then
        insert into spaiati values(:new:numerotelpaziente);
    end if;
end;

Upvotes: 1

Koushik Roy
Koushik Roy

Reputation: 7407

You can try this -

create trigger tampone_trigger
after insert on tamponi
for each row
declare
  v_flag  boolean := false;  
begin

for c in (select numtel from users)
loop
if :new.numerotelpaziente = c.numtel 
    then 
    v_flag := true;
    exit;
     end if;
  exit when no_data_found;
 end loop;
    insert into spaiati values (new.numtel);
end;

Upvotes: 1

Related Questions