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
Advertisement
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