Omer K Omer K - 4 months ago 59
SQL Question

SP2-0552: Bind Variable "NEW" is not declared

I am trying to learn pl/sql triggers. I am trying to create a simple trigger by tracking tutorial http://www.tutorialspoint.com/plsql/plsql_triggers.htm but I got below error. I searched on the internet but could not find the solution. Could you help me on this issue?

CREATE OR replace TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON ok.customers
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
END;
/


Trıgger DISPLAY_SALARY_CHANGES created.

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

PL/SQL procedure successfully completed.


Edit: I am using Sql Developer Version 4.1.1

enter image description here

Answer

Works for me (example from your link, but it's basically the same as your post):

SQL> create table demo (id integer, salary number);

Table created.

SQL> create or replace trigger display_salary_changes
  2  before delete or insert or update on demo
  3  for each row
  4  when (new.id > 0)
  5  declare
  6     sal_diff number;
  7  begin
  8     sal_diff := :new.salary  - :old.salary;
  9     dbms_output.put_line('Old salary: ' || :old.salary);
 10     dbms_output.put_line('New salary: ' || :new.salary);
 11     dbms_output.put_line('Salary difference: ' || sal_diff);
 12  end;
 13  /

Trigger created.

SQL> show errors
No errors.
SQL> insert into demo (id, salary) values (1, 100);
Old salary:
New salary: 100
Salary difference:

1 row created.

SQL> update demo set salary = salary * 1.1 where id = 1;
Old salary: 100
New salary: 110
Salary difference: 10

1 row updated.

In your example it shows

Trıgger DISPLAY_SALARY_CHANGES created.

which doesn't look like SQL*Plus output. What tool did you use?

After that it gives a SQL*Plus SP2-0552 error about an undefined bind variable, followed by

PL/SQL procedure successfully completed.

What procedure was that? I suspect this is the output from a script with some other step that is failing after the trigger is created.

Is the trigger valid? You can normally right-click and check properties in desktop tools, or at the SQL*Plus prompt enter

show errors trigger display_salary_changes