SQL Trigger with cursor not working

Question

I'm doing a sql work but I'm getting trouble with creating a trigger and I have no idea why. Here's the code:

CREATE OR REPLACE TRIGGER artigo_funcionario
BEFORE INSERT ON encomendaartigo
FOR EACH ROW
DECLARE
artigo_invalido exception;
artigo number(3);
auxiliar number(1):=0;
auxiliar_numero number(3);

 CURSOR dotacao_funcionario is
  select a.artigo_id
  from artigo a, familia fa, dotacao d, encomenda e, funcionario f, categoria c
  where e.encomenda_id = NEW.encomenda_id
  and f.funcionario_id=e.funcionario_id
  and f.categoria_id=c.categoria_id
  and d.categoria_id=c.categoria_id
  and fa.familia_id=d.familia_id
  and a.familia_id=fa.familia_id;


BEGIN

  open dotacao_funcionario;
  loop
  fetch dotacao_funcionario into artigo;
  EXIT WHEN dotacao_funcionario%notfound;

  auxiliar_numero := NEW.artigo_id;
  if(artigo = auxiliar_numero) then
    auxiliar:=1;
  end if;

  end loop;
  close dotacao_funcionario;

  if(auxiliar=0) then
    raise artigo_invalido;
  end if;

 EXCEPTION
  WHEN artigo_invalido THEN
    dbms_output.put_line('O funcionário não pode encomendar o artigo introduzido');
END;
/

Be aware that the problem is not with the cursor so don't worry about the tables I'm using. the only one that is relevant is the "encomendaartigo" and it has the following attributes: "encomenda_id", "artigo_id".

The problem is that I'm can't quite use the "new.xxxxx" operator. if ran, the compiler log shows the following:

Error(8,7): PL/SQL: SQL Statement ignored
Error(10,30): PL/SQL: ORA-00904: "NEW"."ENCOMENDA_ID": identificador inválido
Error(25,7): PL/SQL: Statement ignored
Error(25,29): PLS-00201: identifier 'NEW.ARTIGO_ID' must be declared

I've tried everything and nothing seems to be working.

Michael Broughton · Accepted Answer · 2015-11-27 20:19:47Z

2

You are missing the colon before the NEW. e.g.)

auxiliar_numero := :NEW.artigo_id;

Not

auxiliar_numero := NEW.artigo_id;

Optional implementation per your comments:

CREATE OR REPLACE TRIGGER artigo_funcionario
BEFORE INSERT ON encomendaartigo
FOR EACH ROW
DECLARE
 l_check_artigo_id number := 0;

 CURSOR dotacao_funcionario is
  select 1
  from artigo a, familia fa, dotacao d, encomenda e, funcionario f, categoria c
  where e.encomenda_id = :NEW.encomenda_id
  and f.funcionario_id=e.funcionario_id
  and f.categoria_id=c.categoria_id
  and d.categoria_id=c.categoria_id
  and fa.familia_id=d.familia_id
  and a.familia_id=fa.familia_id
  and a.artigo_id = :NEW.artigo_id;


BEGIN

  open dotacao_funcionario;
  fetch dotacao_funcionario into l_check_artigo_id;
  IF l_check_artigo_id = 1 then
    raise_application_error(-20001, 'O funcionário não pode encomendar o artigo introduzido');
  END IF;

  CLOSE dotacao_funcionario;

EXCEPTION
   WHEN OTHERS THEN
      IF sqlcode = -20001 then
         dbms_output.put_line('O funcionário não pode encomendar o artigo introduzido');
       END IF;
        -- a simple RAISE will re-raise the exception
        RAISE;
END;
/

edited Nov 27, 2015 at 20:19

answered Nov 27, 2015 at 18:33

Michael Broughton

4,05516 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Pedro Gonçalves Over a year ago

that's not the problem. I've tried that and whenever I put the colon before the NEW, a box to "enter binds" for that variable appears. That's not the problem then..

Michael Broughton Over a year ago

Trust me, the colon needs to be there. If you are getting prompted for binds then whatever tool you are using is doing you a disservice and assuming that it is executing a script that contains bind variables instead of trying to compile the trigger. If you can't figure out the correct setting to turn off variable replacement in your tool then add the colons, save it to a file, and compile it using the most basic option: Oracle's SQL*Plus.

Pedro Gonçalves Over a year ago

,so you're saying that I should use the colon both in the place you mentioned before and at line 13?

Michael Broughton Over a year ago

Yes, that is exactly what I'm saying

Michael Broughton Over a year ago

Don't know what that's from without seeing the actual script file as it might have an extra trailing byte on a blank line or something. But I assume that you now have a compile trigger as you wished?

|

Collectives™ on Stack Overflow

SQL Trigger with cursor not working

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related