Oracle SP2-0552: Bind variable "NEW" is not declared

Question

I am trying to create a simple trigger but I got below error. I searched on the internet but could not find the solution. Could you help me on this issue?

 create trigger ProcessTigger before insert on T039
 for each row 
 declare consecutivo int; idconsecutivo int; maxconsecutivo int; 
 begin
  select t326c004 into consecutivo from T326 where t326c003 = 'T039' and t326c002 = :new.t039c004;

 if consecutivo is not null 
 then 
 consecutivo :=consecutivo+1; 
 select t326c001 into idconsecutivo from T326 where t326c002 = :new.t039c004 and t326c003=T039; 
 update T326 set t326c004 = consecutivo where t326c001=idconsecutivo and t326c003=T039; 
 else 
 select max(t039c003) into maxconsecutivo from T039 where t071c002=:new.t039c004; 

 if maxconsecutivo is not null 
 then consecutivo := maxconsecutivo+1; 
 else consecutivo:=1; 
 end if; 

 insert into T326 
(t326c002,t326c003,t326c004)values(:new.t039c004,'T039',consecutivo); 

 end if; 
 end;

ERROR:

SP2-0552: Bind variable "NEW" is not declared.

So, which is it? ORA-04071, trigger is missing the BEFORE/AFTER/INSTEAD OF clause? Or SP2-0552, Bind variable "NEW" is not declared? Oracle throws one error at a time, so it **can't** be both. — , Oct 16 '17 at 20:01
sorry I was wrong in the title of the question. The question was Or SP2-0552, Bind variable "NEW" is not declared — Ozz Téllez, Oct 16 '17 at 22:06
Use the small `edit` link below your post to edit it - you can change the title to match your issue. — , Oct 16 '17 at 22:13

score 0 · Answer 1 · answered Oct 16 '17 at 20:45

If this is your idea of "a simple trigger" then I wonder what a complicated one would like?

It seems likely that the SP2-0552 error is because you're running a script with rogue newlines without setting SQLBLANKLINES.

But once you've fixed the syntax errors you'll find your trigger won't run due to a mutating table error. We can't select in a trigger from the underlying table because the state is indeterminate. So this is wrong:

 select max(t039c003) into maxconsecutivo 
 from T039 
 where t071c002=:new.t039c004;

You need to find a different way of implementing whatever business rule that is supposed to do.

I think I exaggerate saying it was simple trigger! I already corrected the settings SQLBLANKLINES but I still get the same error. I need to find a different way? — Ozz Téllez, Oct 16 '17 at 22:21

score 0 · Answer 2 · answered Oct 17 '17 at 07:43

The use of triggers for such function of dispensing IDs is not safe. Remember there could be more than an insert that will race to get the next 'consecutivo' and get the same ID

Also, the issue of mutating table, where you cannot select from the same table in a row-level trigger.

In addition to that, you have syntax error in lines like the below where you're not enclosing T039 with quotes!

select t326c001 into idconsecutivo from T326 where t326c002 = :new.t039c004 
and t326c003=T039; 
update T326 set t326c004 = consecutivo where t326c001=idconsecutivo and 
t326c003=T039;

I suspect the error that you get is due to an invalid reference to a column (when using :new)

You can try the following trigger and function:

Create an autonomous_transaction function to insert the initial "consecutivo"

In the trigger, start with insert (calling the function), and if not creating a record, then update

  create or replace 
  trigger processtrigger 
  before insert on t039
  for each row
  declare
    v_id number;
  begin
    -- start with insert calling the function
    if f_create_new_con(:new.t039c004) = 0 then
      update t326 set t326c004 = t326c004 + 1 -- this is safe since it will take the last committed t326c004 and increase it
      where t326c003 = 'T039'
      and t326c002 = :new.t039c004;
    end if;
  end; 
  /

  create or replace
  function f_create_new_con(p_value in number) return number
  is
    pragma autonomous_transaction;
  begin
    insert into t326 (t326c002, t326c003, t326c004)
    select p_value, 'T039', (select nvl(max(t039c003), 0) + 1 from t039 where t071c002 = p_value)
    from dual
    where not exists (select 1 from t326 where t326c002 = p_value and t326c003 = 'T039');

    -- if no insert then return 0 to update
    if (sql%rowcount = 0) then
      return 0;
    else
      return 1;
    end if;
  end;
  /

Oracle SP2-0552: Bind variable "NEW" is not declared

2 Answers2