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

amjadham77Q

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

Solomon Yakobson

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.

Solomon Yakobson

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.

User_H3J7U

When experiencing syntactic difficulties, check the documentation.

PL/SQL Language guide/create trigger

amjadham77Q

https://community.oracle.com/tech/developers/discussion/comment/16817123#Comment_16817123

https://community.oracle.com/tech/developers/discussion/comment/16817123#Comment_16817123

You're right, it worked ...

thank u bro