Problem with trigger: ORA-01722: invalid number

Hi,

my table DV:
DV_ID VARCHAR2(32) PRIMARY KEY

DV_ID
ONE
XXX
YYY
TOM

Now I'd like to create a trigger that avoid insert the underscore character in dv_id string and raise:
'NOT UNDERSCORE IN DV_ID STRING'

I created this trigger:

CREATE OR REPLACE TRIGGER CHECK_DV BEFORE INSERT OR UPDATE ON DV FOR EACH ROW
declare

counter number;

Begin
SELECT COUNT(*)
INTO counter
FROM DV
WHERE dv_id=instr(dv_id,'_');

IF counter > 0 THEN
RAISE_APPLICATION_ERROR (-20002, 'NOT UNDERSCORE IN DV_ID STRING');
END IF;

End;

but when I try:

INSERT INTO DV VALUES ('XX_YY');

I get this error:
ORA-01722: invalid number
ORA-06512: at CHECK_DV, line 24
ORA-04088: error during execution of trigger CHECK_DV

How can I avoid this error?

Thanks!

Quote:

---

Originally Posted by raf

SELECT COUNT(*)
INTO counter
FROM DV
WHERE dv_id=instr(dv_id,'_');

---

Your DV_ID column is of type VARCHAR2, while function INSTR() return numeric result. So you are comparing string to number, so no wonder you are reciveing "ORA-01722: invalid number".

Besides, your whole trigger logic is wrong. Try something like this:

Code:

---

CREATE OR REPLACE TRIGGER CHECK_DV BEFORE INSERT OR UPDATE ON DV FOR EACH ROW
Begin
  IF INSTR(:NEW.dv_id, '_' > 0 THEN
    RAISE_APPLICATION_ERROR (-20002, 'NOT UNDERSCORE IN DV_ID STRING');
  END IF;

End;

---