CODEWITHSUNDEEP
sqlplsqloracle11g
mskrpan
mskrpan

Reputation: 23

PLS-00049: bad bind variable?

Cant see where i am wrong in my code...Can someone explain me what i am doing wrong? I tryed everything from :new."new_name" to puting prefix of table :NEW.sh_name to :NEW.name dunno what else to do...

Here is table:

  CREATE TABLE sh_auro (
    new_name   varchar2(30),
    old_name   varchar2(30),
    user_name   varchar2(30),
    entry_date   varchar2(30),
    operation   varchar2(30)
  );

and here is superhero table:

create table superhero(
  v_name varchar2(20)
);

here is trigger:

create or replace trigger superhero_auro
before insert or delete or update on superhero
for each row
enable

declare
  v_user varchar2(30);
  v_time varchar2(30);
begin
select user, TO_CHAR(sysdate, 'DD/MON/YYYY HH24:MI:SS') into v_user, v_time from dual;
if inserting then
  insert into sh_auro(new_name, old_name, user_name, entry_date, operation) values(:NEW.new_name, null, v_user, v_time, 'Insert');
elsif deleting then
  insert into sh_auro(new_name, old_name, user_name, entry_date, operation) values(null, :OLD.old_name, v_user, v_time, 'Delete');
elsif updating then
  insert into sh_auro(new_name, old_name, user_name, entry_date, operation) values(:NEW.new_name, :OLD.old_name, v_user, v_time, 'Update');
end if;
end;

and here is error:

Error(7,82): PLS-00049: bad bind variable 'NEW.NEW_NAME'
Error(9,88): PLS-00049: bad bind variable 'OLD.OLD_NAME'
Error(11,82): PLS-00049: bad bind variable 'NEW.NEW_NAME'
Error(11,97): PLS-00049: bad bind variable 'OLD.OLD_NAME'

Upvotes: 2

Views: 14104

Answers (1)

Christian Palmer
Christian Palmer

Reputation: 1302

You're creating a trigger on the superhero table - this does not have columns new_name and old_name - therefore you cannot use :new.new_name or :old.new_name.

What you ought to use is :new.v_name and :old.v_name

Upvotes: 6

Related Questions