-
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!
-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|