Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    9

    Unanswered: Bind variable not declared in trigger

    Dear all,
    i 'm ashek here. i'm trying to implement a before update trigger. my code is as below:

    create or replace trigger EMPLOYEE_BEF_UPD_ROW
    before update on EMPLOYEE
    for each row
    when (new.Salary > old.Salary)
    begin
    insert into EMPLOYEE_AUDIT
    (emp_id,designation,old_salary,new_salary,a_date) values
    (ld.emp_id,ld.designation,ld.Salary,:new.Salary,Sysdate);
    end;

    Everytime i execute the code on Oracle 9i i get the error
    "Bind varibale old not declared"

    I don't have any clue why this is happening. Please help me out. Eagerly anticipating a response from anyone.

    With Regards
    Ashek

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Disregard the fact that I'm using Scott's schema (so table and column names are different):
    Code:
    SQL> create or replace trigger EMPLOYEE_BEF_UPD_ROW
      2    before update on EMP
      3    for each row
      4    when (new.Sal > old.Sal)
      5  begin
      6    insert into EMPLOYEE_AUDIT
      7      (emp_id,designation,old_salary,new_salary,a_date) values
      8      (:old.empno, :old.deptno, :old.Sal,:new.Sal, Sysdate);
      9  end;
     10  /
    
    Trigger created.
    
    SQL> select ename, sal from emp where empno = 7839;
    
    ENAME             SAL
    ---------- ----------
    KING             5800
    
    SQL> update emp set sal = 10000 where empno = 7839;
    
    1 row updated.
    
    SQL> select ename, sal from emp where empno = 7839;
    
    ENAME             SAL
    ---------- ----------
    KING            10000
    
    SQL> select * from employee_audit;
    
        EMP_ID DESIGNATION OLD_SALARY NEW_SALARY A_DATE
    ---------- ----------- ---------- ---------- --------
          7839          10       5800      10000 12.05.08
    
    SQL>
    Obviously, no problem here.

    Could you do the same (i.e. copy-paste your SQL*Plus session)?

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    How did you get away with
    Code:
    when (new.Sal > old.Sal)
    without using colons?
    Code:
    when (:new.Sal > :old.Sal)

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by chuck_forbes
    How did you get away with
    Code:
    when (new.Sal > old.Sal)
    without using colons?
    Code:
    when (:new.Sal > :old.Sal)
    Using colons would raise an error when used in the when clause of a trigger

  5. #5
    Join Date
    Mar 2007
    Posts
    9
    Thanks Littlefoot. it worked for me. i 've tested it on Oracle 10g SQL Plus.

    But u know what for Oracle 9i configuration i 've been using (a pc is the server n 5 other pcs are the client...clients run the isqlplus worksheet) the server pc and 4 client pc fail to execute the trigger, procedure, etc whereas only one client pc manages to execute all thiz properly. the error i get is like "Procedure created with compilation error"
    i don't know why this is happening. do you have any idea? plz share with me.

    With Regards
    Ashek

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does SHOW ERRORS say?
    Code:
    SQL> create or replace procedure it_will_fail is
      2  begin
      3    select count(*) into l_cnt from emp;
      4  end;
      5  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show errors
    Errors for PROCEDURE IT_WILL_FAIL:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/3      PL/SQL: SQL Statement ignored
    3/24     PLS-00201: identifier 'L_CNT' must be declared
    3/30     PL/SQL: ORA-00904: : invalid identifier
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •