CODEWITHSUNDEEP
sqloracle-database
Joao Silva
Joao Silva

Reputation: 138

Error on sql oracle PL/SQL: Statement ignored

This SQL code is giving me the error "Error at line 2: PL/SQL: Statement ignored ", Im working on SQL oracle application express / APEX: I tried everything I can think of and it gives me different problems everytime.

CREATE or replace TRIGGER remove_artista 
   instead of delete on V_ARTISTA
REFERENCING old AS orow
FOR EACH ROW
BEGIN
if exists(select * from Utilizadores where pessoaID = orow.pessoaID) then
        delete from Pessoas where pessoaID = orow.pessoaID;
ELSE
        delete from Artistas where pessoaID = orow.pessoaID;
        delete from Pessoas where pessoaID = orow.pessoaID;
end if;
END;

The view:

create or replace view v_artista as 
 select 
pessoaID, nome_p, sexo, data_nasc, nome_art, biografica
from Pessoas natural inner join Artistas;

EDIT: fixed a litle typo on the code.

Upvotes: 3

Views: 12889

Answers (2)

Joseph B
Joseph B

Reputation: 5679

I don't think you can use the IF EXISTS construct to check if a row exists. You can use SELECT COUNT(*) INTO <a variable>. However, you may not need to check if a row exists. The following code would probably work:

CREATE OR REPLACE TRIGGER remove_artista 
INSTEAD OF DELETE ON V_ARTISTA
FOR EACH ROW
BEGIN
  DELETE FROM PESSOAS
  WHERE PESSOAID = :OLD.PESSOAID;

  DELETE FROM Artistas
  WHERE PESSOAID = :OLD.PESSOAID
  AND NOT EXISTS (SELECT 1 FROM UTILIZADORES WHERE PESSOAID = :OLD.PESSOAID);  

END;

The row from PESSOAS would be deleted in any case. The row from ARTISTAS would be deleted only if the PESSOAID does not exist in UTILIZADORES.

References:

Check if record exists on OTN forum

Upvotes: 2

Luke Woodward
Luke Woodward

Reputation: 65054

The full error I receive from your trigger is as follows:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: Statement ignored
2/4      PLS-00204: function or pseudo-column 'EXISTS' may be used inside
         a SQL statement only

Essentially, the problem is that you can't say if exists(...) as you are doing. Oracle doesn't let you.

Instead, try selecting the number of matching rows in the Utilizadores table into a local variable, and then using that in your if statement:

CREATE or replace TRIGGER remove_artista 
   instead of delete on V_ARTISTA
REFERENCING old AS orow
FOR EACH ROW
DECLARE
  l_count       INTEGER;
BEGIN
  select count(*) 
    into l_count
    from Utilizadores
   where pessoaID = :orow.pessoaID;

  if l_count > 0 then
        delete from Pessoas where pessoaID = :orow.pessoaID;
  ELSE
        delete from Artistas where pessoaID = :orow.pessoaID;
        delete from Pessoas where pessoaID = :orow.pessoaID;
  end if;
END;

I also needed to replace orow with :orow. After making this change as well, your trigger compiled for me.

Upvotes: 6

Related Questions