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!