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

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

(2) Can a Trigger call a Stored Procedure?

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

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

From a Microsoft SQL Server perspective...

SP's offer good protection in terms of a) security - because you can nail
down access rights and b) modularisation - because it far far easiler in
terms of problem diagnosis, change control, development and reuse for you to
put your SQL in a SP rather than embed it somewhere in your application
code. Putting it in the application code means your change control risk is
significantly higher because you need a release of the appilcation
executables rather than a small lower risk stored procedure.

Yes

The rule of thumb is business logic in the app and data access logic in the
database, however, with SQL Server that is shifting because of CLR; SQL
Server is seen more as a data engine / middle tier web service rather than
just a store and retrieve rdbms - search on Jim Gray, he's got some research
on this. There is a good webcast on channel9.com of him discussing the
future.

Depends what you are doing; but think if you are pulling lots and lots of
data across your network (many users may be doing the same) to get page 1 of
'x' when you could just as easily have kept that paging logic inside the SQL
Server threby reducing the amount of data needing to be shifted.


--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"Rohit" <rpk.general (AT) gmail (DOT) com> wrote

"Rohit" <rpk.general (AT) gmail (DOT) com> wrote

Some of the answers depend upon the RDBMS. Some are way too general to give
a definitive answer. (1) is basically a Config Mgmnt issue. (2) in Sybase
ASE a trigger can execute an SP. I believe most other RDBMSs have this
capability. (3) there is no "must" SQL-wise. (4) Ambiguous question, but,
in general, RDBMS set-processing is faster than RDBMS row-processing which
can be faster than Client row-processing (owing to greater network IO).

I'm a DBA and IMO SPs offer more flexibility. If you find a
poorly-executing SQL statement which code do you want to test and change? A
stored procedure, an App Server application, or an application on multiple
clients? The last is generally the worst. How do you want to examine
schemas versus app SQL (especially with a design tool)? If most of it is in
the DB it's easier - although some tools can handle the DB and clients
pretty well. If there are argument/parameter changes then it can be tough
with any of the options.