How To Modify The Type of the :new Variable Inside a Create Trigger Before Insert in ORACLE/PLSQL

Question

I defined the column YEAR in my table STORE with datatype int. The data is inside a csv. When inserting i pass the year as a string, because it is either a number or 'N/A'. I tried declaring a trigger to correct this

It should do the following: If the inserted string is 'N/A' Insert NULL. ELSE insert the value of the string.

CREATE TRIGGER checkYear
BEFORE INSERT ON STORE
FOR EACH ROW BEGIN
    IF :new.YEAR = 'N/A' THEN
        :new.YEAR := NULL;
    ELSE
        :new.YEAR := CAST(:new.Year AS INT);
    END IF;
END;

When inserting values i get a trigger error from checkYear (ORA-04098) inside SQL Developer compiling the trigger gives PLS-00382

Answer 1

The data type of the :new.year variable will always match the data type of the column in the table. You must filter or parse your strings into integers before placing them in the SQL statement.

Answer 2

You just can use a REPLACE() function in order to get rid of the case whenever upcoming year value equals to N/A such as

CREATE OR REPLACE TRIGGER checkYear
  BEFORE INSERT ON store
  FOR EACH ROW
BEGIN
  :new.year := REPLACE(:new.year,'N/A');
END;
/

Otherwise(if it's a year literal which can be convertible to a numeric one such as '2022' ) no need an explicit casting to a numeric value which's already found out implicitly by the DB.

Btw,

• you can prefer to adding that to your Insert statement such as

  INSERT INTO store(...,year,...) VALUES(...,REPLACE(year,'N/A'),...)
  

  rather than creating a trigger.

or

• you can cast directly within the control file such as

    ... 
    year  CHAR(4000) "REPLACE(:year,'N/A')" 
    ...  

if your importing tool is SQL loader.