Forum Stats

  • 3,875,405 Users
  • 2,266,911 Discussions
  • 7,912,196 Comments

Discussions

ORA-25000: invalid use of bind variable in trigger WHEN clause

amjadham77Q
amjadham77Q Member Posts: 3 Green Ribbon
edited Nov 26, 2021 8:19PM in SQL & PL/SQL

Hi, everybody 👋 ...

I'm trying to create a trigger with a when clause inside its header, which contains the condition that will fire the operator.

trigger idea is to insert empno, ename, old_sal,new_sal into tempTable values (:new.empno, :new.ename, :old.sal, :old.sal); when the difference between old and new salary is more than 1000.

How can I do this using a when clause in the trigger head

If there are other methods that are more effective than when clause in the trigger head, that would be great

Thanks in advance

---------------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER up_2th_diff_trg

 BEFORE

 UPDATE

 ON EMP

 FOR EACH ROW

 WHEN (:NEW.sal - :OLD.sal > 1000)

BEGIN

 INSERT INTO EMP_LOG (empno,ename,old_sal,new_sal) VALUES (:NEW.empno,:NEW.ename,:OLD.sal,:NEW.sal);

 DBMS_OUTPUT.PUT_LINE('Trigger task completed successfully');

END;

/

------------------------------------------------------------------------

ORA-25000: invalid use of bind variable in trigger WHEN clause

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,951 Red Diamond
    Answer ✓

    You don't use colon in WHEN clause:

    CREATE OR REPLACE
      TRIGGER up_2th_diff_trg
        BEFORE UPDATE
        ON EMP
        FOR EACH ROW
        WHEN (NEW.sal - OLD.sal > 1000)
        BEGIN
            INSERT
              INTO EMP_LOG(empno,ename,old_sal,new_sal)
              VALUES(:NEW.empno,:NEW.ename,:OLD.sal,:NEW.sal);
            DBMS_OUTPUT.PUT_LINE('Trigger task completed successfully');
    END;
    /
    

    SY.

    amjadham77Q

Answers