CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Omer
Omer

Reputation: 10194

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

Upvotes: 2

Views: 15243

Answers (4)

Jaume VG
Jaume VG

Reputation: 1

I think it is missing the "REFERENCING NEW AS NEW OLD AS OLD" sentence:

  CREATE TRIGGER [trigger_name]
  BEFORE INSERT OR UPDATE ON [table_name]
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

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

Upvotes: 1

Sathish Kumar
Sathish Kumar

Reputation: 81

Can you please check you column name. I have tried your code below and I got output.

create table test
(
no number(10),
sal number(10)
);

CREATE OR replace TRIGGER test_tr 
  BEFORE DELETE OR INSERT OR UPDATE ON test 
  FOR EACH ROW 
DECLARE 
    sal_diff NUMBER; 
BEGIN 
    sal_diff := :NEW.sal - :OLD.sal; 
    dbms_output.put_line(sal_diff);
END; 
/ 



insert into test values(1,100);

update test set sal=200 where no=1;

Output :

1 rows inserted.


4 rows updated.
100
100
100
100

1 rows inserted.

Upvotes: 1

XING
XING

Reputation: 9886

Try this:

CREATE OR replace TRIGGER test_trg 
  BEFORE DELETE OR INSERT OR UPDATE ON test 
  FOR EACH ROW 
DECLARE 
    sal_diff NUMBER; 
BEGIN 
    sal_diff := :new.d - :old.d; 
END; 
/

Upvotes: 1

Related Questions