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

5-6. Validating Input Data

Problem

You want to validate data before allowing it to be inserted into a table. If the input data does not pass your business-rules test, you want the INSERT statement to fail. For example, you want to ensure that an e-mail address field in the EMPLOYEE table never contains the domain portion of an e-mail address, in other words, that it never contains the @ character or anything following the @ character.

Note Recipe 5-7 presents an alternative solution to this same problem that involves silently cleansing erroneous data as it is inserted.

Solution

Generally speaking, do validation using BEFORE triggers, because ...