Re: Writing most code in Stored Procedures

Rohit wrote:
> I have few queries regarding the use of Stored Procedures, Functions
> and Triggers in an RDBMS.

These are all easy questions to answer: "it depends".

OK, so you might want some reasons...

> (1) When to use Stored Procedure? Writing an INSERT query in a Stored
> Procedure is better or firing it from the application level?

Some people like to have all access to the database through stored
procedures. This can make it simpler to control access to the data and
seems popular with MSSQL developers.

Personally, I use SQL as my interface - that's its purpose. Actually,
that's not quite true, I use various wrappers that generate SQL for most
queries.

> (2) Can a Trigger call a Stored Procedure?

It has to, although they're just called functions in PostgreSQL, can be
written in a range of procedural languages and take place within a
transaction the same as any other function-call.

> (3) What type of code must reside in application and what type in
> RDBMS?

Anything that's to do with data integrity I put in the database.
Anything that's to do with process I put in the application (or
middle-ware layer).
The tricky bit is deciding what to do when you have something that's a
bit of both.

> (4) Is it faster to work at application level or at the database level?

Probably faster in the database, assuming you have only one machine. If
you have more than one machine then you can have each machine designed
for its purpose. Of course, faster to run might be at the cost of more
difficult to write. It might also mean your process code can crash the
database server.

--
Richard Huxton
Archonet Ltd