I have a trigger to trim values using trim(:new.value) before inserting but what if one record is updated and by mistake a space is added, i'm sure a update trigger is not recommended as we won't ever know which column was updated. is there a way to always store trim(value) by default? regardless of insert or update..
Use an API. By that I mean don't allow anyone direct access to the table for DML, only allow DML via a set of procedures, grant execute on those procedures to whomever needs to perform DML on the table. Within the procedure, you can trim all the values that you see fit prior to the actual insert or update actions.