Oracle Error ORA-00922 While Compiling Trigger

Question

I have a varchar2 datatype column in a table. User only can insert number to that column and I don't want to change as number. Sometimes while inserting data this column as manual, user can space in column. I create a trigger to avoid it. Trigger i wrote is as below.

CREATE OR REPLACE trigger_name
BEFORE INSERT ON table
FOR EACH ROW 
BEGIN 
  IF :new.column != TRIM(:new.column) THEN
     :new.column := TRIM(:new.column);
  ELSE
   dbms_output.put_line(:new.column || ' is suitable for jobid');
END IF;
END;

I got error like below while compiling above code.

"ORA-00922: missing or invalid option"

Thanks in advance.

Unfortunately, it keeps getting error. I deleted the semicolon at end of the 8th as well, but I couldn't fix it. — bcdyzi
Firstly, I only deleted for 6th but i got error. Then i also deleted for 8th, however i got error again. — bcdyzi
Excuse me I misread, there's a problem with the syntax that TRIGGER keyword is missing after CREATE OR REPLACE , and table name which should be a reprentation I think. Since it's not possible to create a table with name table. Do you need a floating number for that column, or just digits ( integers ) ? — Barbaros Özhan
you're welcome.If you're insisting on not changing the data type, perhaps adding a check constraint would be a better alternative than using trigger I think. Do you need a floating number for that column, or just digits ( integers ) ? — Barbaros Özhan
Your trigger will not remove all spaces from the data entered; TRIM, as used, removes leading and trailing spaces. However it will not any spaces between the first and last digits. So the string '123 ' becomes '123', the string '12 3' does not, the space remains. Also how do you pervert the user from entering other non-numeric characters. If you can do that why not also disallow entey of a space. BTW your trigger can be reduced to the single line :new.column := trim(:new.column); to produce exactly the same result, except for the message that nobody will ever see, beyond development. — Belayer

Junjie Junjie · Accepted Answer · 2020-08-18T09:31:09

The error ORA-00922 is caused by the missing keyword TRIGGER, as pointed out by Barbaros Özhan. Just add it, assuming you got the table name and column name correct, then it would do fine.

But going further your inquiry, it seems you wanted to replace all the spaces entered by the user for that column. If so, this trigger could do it:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  IF :new.column_name != REPLACE(:new.column_name,' ','') THEN
    :new.column_name := REPLACE(:new.column_name,' ','');
  ELSE
    dbms_output.put_line(:new.column_name|| ' is suitable for jobid');
  END IF;
END;

I added the missing keyword trigger. Just replace the table_name and column_name to their correct names and it should work.

Oracle Error ORA-00922 While Compiling Trigger

1 Answers