Search code examples
plsqltriggerstrim

PL/SQL Perfom TRIM inside of a trigger


I am trying to make a trigger that when data is inserted, it trims the data that is being inserted.

Here is what I am trying ..

CREATE OR REPLACE TRIGGER trig_trim
BEFORE INSERT ON triggertest
FOR EACH ROW

BEGIN
  TRIM(:new.testchar);  
END;
/

I do an insert like this

INSERT INTO triggertest (testnum, testchar) VALUES (9, ' r9 ');

and I am getting this error...

04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

When I just run the code to create the trigger I get this

TRIGGER TRIG_TRIM compiled
Errors: check compiler log

and in the compiler log it says "'TRIM' is not a procedure or is undefined"

Is my syntax wrong or my logic? I don't know why this is failing.


Solution

  • TRIM has to return the result to something. I think you want:

     :new.testchar := TRIM(:new.testchar);