Expression is of wrong type error message while compiling trigger

I am getting the below error. Don't know why this error is popping up. Please suggest:

SQL> CREATE OR REPLACE TRIGGER test_trigger
  2
  3    BEFORE INSERT OR UPDATE OF DRIVER_NUM
  4
  5    ON   test_driver  FOR EACH ROW
  7    DECLARE
  8
  9    Invalid_Number EXCEPTION;
 10
 11    PRAGMA EXCEPTION_INIT(Invalid_Number , -01722);
 12
 13     BEGIN
 14   
 15        IF TO_NUMBER(:NEW.DRIVER_NUM) THEN
 16   
 17            :NEW.DRIVER_NUM := TO_NUMBER(:NEW.DRIVER_NUM) ;
 18
 19        END IF;
 20
 21        EXCEPTION
 22
 23           WHEN Invalid_Number THEN
 24
 25              :NEW.DRIVER_NUM := NULL;
 26
 27      END;
/
 28
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER TEST_TRIGGER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/2      PL/SQL: Statement ignored
9/5      PLS-00382: expressi开发者_运维问答on is of wrong type

---

IF TO_NUMBER(:NEW.DRIVER_NUM) THEN is not a valid expression.

I guess you want something like:

IF TO_NUMBER(:NEW.DRIVER_NUM) > 0 THEN

But what's more important, your code doesn't make sense at all. I don't understand why you assign the contents of a column to the same column itself:

 :NEW.DRIVER_NUM := TO_NUMBER(:NEW.DRIVER_NUM);

If DRIVER_NUM is already a numeric column, that this doesn't make sense at all (converting a number to a number?).

If DRIVER_NUM is e.g. a VARCHAR then this makes even less sense

---

My guess is you are trying to ensure that a VARCHAR2 column DRIVER_NUM contains only valid numbers - if not, set it to null. If that's what you want, try this:

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT OR UPDATE OF DRIVER_NUM
ON   test_driver
FOR EACH ROW
DECLARE
    Dummy NUMBER;
    Invalid_Number EXCEPTION;
    PRAGMA EXCEPTION_INIT(Invalid_Number , -01722);
BEGIN
    Dummy := TO_NUMBER(:NEW.DRIVER_NUM) ;
EXCEPTION
    WHEN Invalid_Number THEN
      :NEW.DRIVER_NUM := NULL;
END;

---

Try leaving out "OF DRIVER_NUM" and using it like this:

/* Formatted on 1/06/2011 9:10:59 (QP5 v5.114.809.3010) */
CREATE OR REPLACE TRIGGER test_trigger
   BEFORE INSERT OR UPDATE ON test_driver
   FOR EACH ROW
DECLARE
   INVALID_NUMBER EXCEPTION;
   PRAGMA EXCEPTION_INIT (INVALID_NUMBER, -01722);
BEGIN
   IF TO_NUMBER (:NEW.DRIVER_NUM)
   THEN
      :NEW.DRIVER_NUM := TO_NUMBER (:NEW.DRIVER_NUM);
   END IF;
EXCEPTION
   WHEN INVALID_NUMBER
   THEN
      :NEW.DRIVER_NUM := NULL;
END;

If I understand what you are trying to do, you wanted to create a trigger on a certain field only? This is not necessary, or even possible for that matter afaik.