Oracle and PL/SQL Recipes: A Problem-Solution Approach

5-8. Replacing a Column's Value

Problem

You want to verify that a column value is in the correct format when it is entered into the database.  If it is not in the correct format, then you want to adjust the value so that it is in the correct format before inserting into the database. For example, upon creation of an employee record, it is essential that the e-mail address follows a certain format.  If the e-mail address is not uniform with other employee e-mail addresses, then it needs to be adjusted.  You want to write a trigger that ensures that the new employee EMAIL value will be in the correct format.

Solution

Check the format using a BEFORE trigger. For this recipe, use a BEFORE INSERT trigger to determine whether the new EMAIL value is ...