PL/SQL: What is ERROR: ORA-04082: NEW or OLD references not allowed in table level triggers

Question

Hello i have some problems to do a pl/sql trigger . I want to do a trigger that do a "history". I create a table that i record the sout of the trigger with this code:

create table control(
camp1 varchar2(255) 
);

It doesn't works yet but we are close. Now with help of user:7623856 when i execute this is what appears enter image description here I work in SQL Developer and oracle 11

This is the new code

create or replace trigger t_auditar_alta_empleat
after insert on empleats
for each row
begin
  if :new.codi_dept is null then 
  insert into control values('Alta empleat amb codi' || :new.codi_emp);
  else
  insert into control values('Alta empleat amb codi' || :new.codi_emp || 'amb el codi de departament '|| new:codi_dept);
  end if;
end;

And appears a new error.

*LINE/COL ERROR

5/3 PL/SQL: SQL Statement ignored 5/109 PLS-00049: bad bind variable 'CODI_DEPT' 5/109 PL/SQL: ORA-00917: missing comma Errores: comprobar log de compilador*

Belayer Belayer · Accepted Answer · 2020-04-30T16:40:27

Your almost there, but as defined you current have a statement level trigger, but you need a row level trigger. See here.

create or replace trigger t_auditar_alta_empleat
after insert on empleats
for each row        -- added 
begin
  if :new.codi_dept is null then 
  insert into control values('Alta empleat amb codi' || :new.codi_emp);
  else
  insert into control values('Alta empleat amb codi' || :new.codi_emp || 'amb el codi de departament '|| new:codi_dept);
  end if;
end;

PL/SQL: What is ERROR: ORA-04082: NEW or OLD references not allowed in table level triggers

1 Answers