At 02:16 AM 6/12/2004 +0000, you wrote:

>Hi,
> > I'm currently using SPs to allow insertions on a particular table (the
> > main SP calls a number of other SPs to perform insertions into
> > additional related tables when necessary), and I'm wondering if a lot
> > of the functionality (ie data validation/integrity checking etc) that
> > I'm writing into the SPs might be better located in triggers for the
> > tables in question.

Yes, indeed. In fact, it's often (though not always) the case that you can
hand over the entire functionality of such SPs to the triggers. A trigger
can execute SPs and you have the added benefit of having access to the OLD
and NEW context variables.

>The current approach works fine (so far), so is
> > there a good reason for me to move any of the existing functionality
> > into triggers? The db will only ever be accessed via php and Delphi,

We've all said that in past... :-) Don't overlook the possibility that
some nincompoop member of your customer's staff will download a suitable
tool and attempt direct updates of user and (God forbid!) system
tables. Triggers give you absolute control over what happens as a result
of a DML request, whether it comes from your applications or external
forces that you don't know about.

> > and I can control the ways in which it is accessed (ie insertions only
> > performed via SPs if I so choose).

Don't overlook the fact that this assumption is only safe if you can
absolutely guarantee that nobody in the customer organisation is able to
log into the database as sysdba or Owner and use a freely-available admin
tool to shatter your illusions.

>I understand that using triggers
> > offers me transaction safety, ie if one of the triggers in the process
> > fails, the whole transaction will be rolled back - am I unable to do
> > this with SPs?

Oooh, how does one answer that? "No, you are not unable to this with SPs."
:-)
The work of an SP is atomic: nothing that the SP does will get committed
if the transaction is rolled back.

>I guess what I'm wondering is: what will triggers give > me that stored
>procedures won't?

Control over what happens *every time* a row is inserted, updated or
deleted. Relying on SPs invoked from clients does not give you any control
over what happens if the logically-disabled are let loose in the
database; or if a third party comes along and writes another SP that does
some or all of your stuff with different outcomes. You then have the
situation where the integrity of your design depends on whether there is an
"r" in the month or something similarly arbitrary.

Programmatically, access to the NEW and OLD values for validation and
cross-checking is indispensable. It's not available to SPs.

./heLen