---------- Original Message -----------
From: Tom Wright <tom@...>

> It seems sensible to me to use procedures to validate my data before
> inserting it into the tables, I have two main questions.

------- End of Original Message -------

Don't forget about the other layers at which you can do validation:

- field types, including domains
- procedure parameter types, including domains
- check constraints
- before-insert/update/delete triggers
- on-commit triggers

I recommend you use procedures to model "what" is being requested: certain
parameters together, certain actions bundled in sequence, a request from a
specific user or system, ... But then I recommend you put all the universally-
true validation logic in constraints and triggers, so you can easily create new
procedures for new scenarios, without duplicating or accidentally circumventing
your validation code.

You can also "hide" some validation and business rules with updateable views: an
end-user or program need not know that a simple "insert" command is actually
going through procedural code before ever doing anything. (See "instead-of
trigger" or "rule" in other products.)

-Philip