Error on sql oracle PL/SQL: Statement ignored

oracle, sql

Solution

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.

Problem

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.

Original source