Ditto - I've got 3 rules that I try to follow wherever possible:

1.  When I've got database-internal stuff that has to happen *as a
result of data access or changes*, use triggers.  The classic example
is an audit trail; if I have to keep track of who does what to various
tables in the database, I use one or more triggers to create entries
in an "audit" table automatically whenever e.g. an UPDATE occurs to a
table.  I want the database to be taking care of that stuff for me; I
don't want to be relying on users or external apps to do it for me

2.  Nobody except the DBA gets SELECT/UPDATE/DELETE/INSERT access to
the database; they get access to the specific set of stored procedures
they need to do what they need to do.  Even if I'm the DBA, I use
stored procs whenever possible.  There's many reasons for this; I can
tune or tweak stored procs for performance without breaking code, I
can limit what people are able to do to the smallest subset necessary,
I can ensure that stupid 10-way JOINs are prevented altogether, I can
build all sorts of error checking into the stored proc and *know* that
it can't be bypassed by users, I can ensure that indexes are searched
in the appropriate order and we aren't doing loads of table scans when
they're unnecessary just because a user has his SELECT/WHERE clauses
in the wrong order, ...

3.  Except for the simplest databases, people look at the data using
views, not SELECTS from tables.  With a view, I can show them exactly
the data they need to see, and hide all the stuff they don't want to
(or shouldn't) see.

These aren't rules I came up with in isolation - many DBAs work
strictly to these rules, because they can remove the cause of lots of
pain down the track.

For an example of rule 2, consider the case where you've got 3 tables:
- customers
- items (i.e. a bunch of things you're selling)
- customeritems (i.e. which items have been ordered by which customer)

If a new customer fronts up and wants to buy something, without stored
procs I'm faced with doing the following:
- INSERT INTO CUSTOMERS ...
- SELECT cust-id FROM CUSTOMERS WHERE cust-name='...'
- SELECT item-id FROM ITEMS WHERE item-description='....'
- INSERT INTO CUSTOMERITEMS (cust-id, item-id, qty)

With stored procs, I can do this instead:
- EXEC ADDCUSTOMERITEM('Bob Smith', 'Mexican rug', 7)
and have the ADDCUSTOMERITEM stored proc do all the behind-the-scenes
work for me.  It'll search for 'Bob Smith', and add him if he isn't
already a customer; it'll search for 'Mexican rug' and get the
item-id, then it'll add an entry to CUSTOMERITEMS pointing to Bob's
cust-id, the Mexican rug's item-id, and the quantity required.  I can
also bolt in a bunch of error checks to ensure e.g. the customer's
name is less than 30 characters long, that the customer was added
correctly, that qty isn't a negative number, etc.

I code up ADDCUSTOMERITEM once, throw in a big pile of error checks,
test it, and then I simply call it over and over again from any app I
or anyone else may write in the future.

If I have to rely on developers doing the above
INSERT/SELECT/SELECT/INSERT waltz in their code, suddenly I'm adding
the opportunity for them to make mistakes.  Furthermore, if I need to
add a couple of extra fields to the CUSTOMERS table later on, I can do
so and hopefully change the ADDCUSTOMERITEM stored proc so that
EXEC ADDCUSTOMERITEM works exactly as it did before I added the fields
- no user code needs to be changed.  The end result is much more
maintainable.

For an example of rule 3, consider a table called EMPLOYEES.  Without
an appropriate view, users may do SELECT * FROM EMPLOYEES and get all
the details of what's in the EMPLOYEES table.

What happens if, later, I add a SALARY field to EMPLOYEES?  Now I
don't want people to be able to do a SELECT * FROM EMPLOYEES, because
then they'll see what everyone earns - not good!  I have to tighten up
security, and that'll almost inevitably break apps that are hitting
the EMPLOYEES table.

A better solution is to have a view called e.g. VIEWEMPLOYEES, which
is just a SELECT NAME, PHONE, EMAIL, ... FROM EMPLOYEES under the
covers.  If I remove users' ability to do a SELECT ... FROM EMPLOYEES,
but give them SELECT ... FROM VIEWEMPLOYEES, they won't notice any
difference.  However, if I add a SALARY field to EMPLOYEES later, the
VIEWEMPLOYEES view won't change because SALARY isn't in its SELECT
NAME, PHONE, ... list.  I can create a separate view
VIEWEMPLOYEESPLUSSALARY and give access to it to specific
people/groups who need to see salary data, and limit it to only them.

All this stuff works in Postgres, and has for years.  I can
INSERT/DELETE/UPDATE a VIEW, if I get a bit clever, which is
particularly handy for Rails.  I'm still working out how best to use
Postgres with Rails, but that's fine - Rails is massively more
productive than anything else I've tried, and I can put up with a bit
of trial-and-error to get the result I want with Postgres.

It sounds like MySQL is heading in that direction as well now, which
will be really cool; suddenly an extra option is emerging, which is
never a bad thing.  MySQL is really well supported by Rails, plus
available at ISPs everywhere, plus there's any number of books and
reference material available if/when you need them.

I like MySQL; I just like Postgres more.

Dave M.

On 6/19/05, Robby Russell <robby@...> wrote:
> On Sat, 2005-06-18 at 11:45 -0500, Rick Olson wrote:

> You might not. But, I use them quite a bit. Triggers, functions, etc...
> are quite useful and can keep your application finely tuned.
> 
> > Doing so would limit you to PostgreSQL for your app.
> 
> If I am building an open source app, thats one thing... If I am building
> a solution for a client, I take advantage of the programs that I use.
> Using procedural languages is something that I do need for projects.
> 
> Sometimes it makes more sense for me to put some logic in the db, rather
> than in my Rails code. It's like using views, but more intelligent
> views. I never assume that my Rails interface will be the sole interface
> to that data. Often times, a client might need a GUI program for some
> tasks and it's much easier to call a function in the DB rather than
> rebuild it for each interface/application.