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

5-7. Scrubbing Input Data

Problem

You are interested in examining and correcting user input prior to it being inserted into a database table.

Solution

Use a BEFORE INSERT trigger to scrub the data prior to allowing it to be inserted into the table. By using a trigger, you will have access to the data before it is inserted, which will provide you with the ability to assess the data before it is persisted.

In this particular example, a trigger is being used to examine the data that was entered on a form for insertion into the EMPLOYEES table. The e-mail field is being validated to ensure that it is in a valid format. In particular, the e-mail field for the EMPLOYEES table includes only the address portion to the left of the @ symbol. This trigger ...