1.8.4.4 Stored Procedures and Triggers

Stored procedures are being implemented in our version 5.0 development tree. See section 2.3.4 Installing from the Development Source Tree.

This effort is based on SQL-99, which has a basic syntax similar (but not identical) to Oracle PL/SQL. In addition to this, we are implementing the SQL-99 framework to hook in external languages.

A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side.

Triggers will also be implemented. A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transactional table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

User Comments

Triggers and Stored procedures are wonderful tools,
but they come with a price.

Enforcing the business rules at the database level
allows you to be confident that the data conforms.

There are a few cautionary notes however.

The first is that you will probably end up creating
your own procedural language which will differ from
the other procedural language and start to introduce
further discrepancies. If possible, using an
existing language and creating an interpreter for
it would be preferable. Although I am fluent in Oracle
PL/SQL and SQL Server 2000 T-SQL, I know that I would
really rather not be. It would be an advantage to me
to be able to use a standard language so that I could
leverage those skills elsewhere. A database where I
had to learn yet another proprietary instruction set
would make me dubious of wanting to get involved with
it.

Performance will degrade and it will take awhile to
get it to a satisfactory level. This does not take
anything away from the development team, it has been
true of every database system when they have done this.
DB2 still has a reputation of being slow and a memory
hog when you use their triggers and stored procedures.
Teradata only recently added the ability to use them
and it implemented them so poorly that nobody wants
them. Be prepared for the performance hit and the
resulting aftermath.

A possible way around this would be to include further
support for server software that applies the business
rules without actually being on the database. J2EE,
for example, could be utilized with Enterprise Java Beans
and have the exact same net effect as having stored
procedures and triggers, without actually having to
modify the database.

Just a few thoughts,
Thanks,

Brad

Triggers and stored procedures can be very important in
a large corporate development environment. Typically
one group is responsible for the database, and multiple
others for client software. Using triggers to guarantee
business rules, and providing stored procedures for
clients, allows the database group to enforce data
integrity. Otherwise you will have bad data, I don't care
how good your people are.

I come from a MS SQL 7 background, and I've made
extensive use of SP's and triggers. They're an
excellent tool. Also the SQL is pre-compiled so
there isn't any lag time for compiling. Just got
to watch out for triggers however, as they make
debugging difficult! Make sure you have an
output statement in each one, so if you do hit a
trigger you'll know which one.

I also hate triggers and I appreciate the simplicity and speed
of MySQL. However, in certain corporate situations where I
need an app I am creating to receive certain data whenever
a table changes, and I have no control over the app that is
making the changes on the other side, I really must have
triggers. In some situations I have had to tell people that
they have to migrate to another database if they can't live
without my app or let me alter their existing app. So, I say
MySQL needs triggers, but keep them optional on install, and
only use them with goofball clients that force you to use
them.

MySQL needs 1. Views , 2. Stored Procs , 3. Triggers. I agree
with the previous posters, keep the proc engine a seperate process from the db (kinda like an app server), that way only those that make use of procs or triggers suffer performance degradations, also if scalability is called for the engine can be run on seperate boxes.. Im sure the developers are working hard with these issues.. best not to have them rush some crap out just to satisfy the throngs of needy developers and DBA's.. keep up the fine JOB....

MySQL does NOT need stored procedures.

I don't buy into the 'it's compiled so it's faster' argument some of you have posted.

Instead, implement bind variables and db caching, keep all you data tier logic in a data tier component (Java/C#/whatever) and achieve equivalent performance to a compiled stored procedure.

Creating a web app before .NET and MySQL, a group had to be knowledgable in javascript, html, middleware language, and PL/SQL, T-SQL (insert your db langauge here).

Now, all I have to know is C# and...nothing else. Let's keep it that way. Last thing I want right now is to pick up yet another scripting langauge.

MySQL DOES need Stored Procedures, but NOT Triggers.

Stored Procedures allow server side control using CURSOR and loop structures. Better to keep all logic in one place
rather than using an external script language. Stored Procedures can be used as substitutes for both Views and Triggers. They are more versatile than Views and clearer and easier than Triggers.

Stored Procedures and Triggers certainly do have their place in an efficient, <i>enterprise</i>-level database solution. Without stored procs, the job of creating and managing server-side database updates, such as summation tables in a reporting dbs or processing bulk inserts would be tedious at best and costly at worst. It is the job of the programmers/architects/dbas not only to use the technology best suited for task, but to understand both the advantages and disadvantages associated with their chosen tech.
Good Job, MySQL team! I'm eagerly awaiting "5.0 or so"

I agree that stored procedures should ideally be implemented in a way that does not penalise users who do not use them. But personally, I would like to see them in MySQL ASAP. The view that databases should just store and enable access to data in tables and nothing more strikes me old-fashioned. Fair enough, procedural syntax is not uniform between products, but that won't last forever.

Stored procedures have their place in a complex database application. In OOP, there are plenty of advocates of hiding member variables and exposing functionality only via member methods. Unless taken to extremes, this is sound design practice. In a database this translates to hiding tables and views and exposing functionality via stored procedures. In addition, once a database models sufficiently complex objects, bare tables are likely to present data at too low a level and in a fragmented fashion. Table structures are largely dictated by the requirements of normalisation which does not necessarily have much in common with data objects a high-level external application operates with. Views can alleviate this to a large extent, but once any kind of business logic needs implenenting, a stored procedure becomes a necessity. And if a piece of business logic depends only on database data, it should go in the database. Business logic should step outside the database once it relies on more than just database data, say it needs to consume a web service.

Triggers are only necessary where an application has to share a database with others who manipulate data at table level rather than via stored procedures (and cannot be persuaded not to). Otherwise they become an error-prone and lazy replacement for stored procedures.

Keep up the good work, MySQL is an admirable effort. I am looking forward to working with verstion 5.0.

My I be so bold as to suggest following the PHP and Apache model of letting the users add support:
1. mod_Before_Trigger
2. mod_After_Trigger
3. mod_SP (Stored Procedure)
4. mod_Views
5. mod_Updatable_Views
6. mod_SubSelect

- This model resolves many issues between those that believe a database is a garbage can while allowing those of us that believe in Cobb's rule Zero (0) to be satisfied. Using the above model you can create a dynamic catalog allowing the creation of views and constraints making mySQL a true relational database.

To those of you that support Stored Procedures, and Triggers, I applaud your professionalism and caveats on their requirement and use. It is entirely true that sloppy programming practices lead to poor performance. Stored procedures and triggers do not in and of themselves.

To those of you that do not support Stored Procedures and Triggers for MySQL, I do not know what planet you come from, but it is entirely obvious that you are still living in the dark ages.

Current SQL standards are set at SQL99 or SQL3 with more and more vendors supporting a conformance level 0 or 1 at the very least. Stored Procedures and Triggers are part of this standard. Whether they are implemented are another story.

In addition, the use of the old E-R data model by itself is no longer advocated. Instead the E-R model is a jumping off point for what's known as a semantic data model, which a) more UML compliant, and b) lends itself to the application and reporting needs of the users by identifying what groupings of data makes sense to the user and what information the user might have on hand to retrieve or update said information.

That being said, the most vendors in addition to traditional triggers allow system architects to control the complete behavior of the database, during operations such as INSERT, UPDATE, and DELETE and to replace the traditional operation entirely.

Traditional triggers could only capture the data and save it elsewhere. For logging or auditing or cascading data updates or other purposes.

These new triggers allow a database to have triggers on views, which would then actually do the update to the underlying tables.

Or to prevent deletes altogether and to flag the row as inactive during a DELETE request.

How can you in all honesty suggest that a tool, in and of itself is evil, unnecessary or useless, when its purpose and its needs can be shown mathematically as well as in practice?

It seems to me that this is more a reaction from development of monolithic applications where the developer is used to having full control and therefore needs control of the data in the application.

In the real world, applications make requests. The database decides what's legal. This is a logical separation of concerns. Otherwise, you'd be building business logic at every step of every application and we wouldn't need anything like .Net or J2EE to package business rules in a middle tier.

Don't get me wrong. Not every business rule goes into the database. But the one's that do go in make sure that you don't get garbage in, and that your applications are relatively easy to maintain.

Imagine the difficulty when you have a report that pulls in data from 4 different tables and needs to have the data indexed and to have the data be updatable through a form.

Now tell me, how many applications will you modify when there is a structural or data format or data source change, to make sure the application doesn't break. And tell me how many test suites you'll have to run.

And compare that to the one instance of a view in a database, with triggers and indexes which only need to be modified at a single central point.

I would agree that stored procedures and triggers CAN make a database slow. It is up to the DBA to make sure they do a SHOW PLAN, or an EXPLAIN or what not to determine what trade-offs are present and how best to optimize the data. Because no matter what, whether you have stored procedures and triggers or not, sloppy code will always be slow.

Any more questions?

I welcome any responses grounded in theory or fact. Please don't come complaining to me about how your personal lack of desire to use Stored Procedures and Triggers mitigates your desire to not see them implemented in MySQL.

Another thought would be to keep the stored proc engine separate, sort of like what PHP is doing with the Zend engine. In fact, it might be worth considering talking to Zend about doing just that. Otherwise, stored procs/triggers will likely cause the expected performance hit because of disk i/o (meaning that caching the little beggars in memory would be a really good idea if used with an intelligent caching scheme).

using an existing language as a scripting language or an interpreter for handling triggers and SP is a good idea.

cross platform C/C++ interpreter (Ch) from
www.softintegration.com is an option. it is free to use
in linux, unix and mac os x.

This dialog seems to be a good place to see the scale of projects that various people work on -its where we separate the small-to-medium scale project designers from the nerve center, enterprise engineers.

Triggers and other database-embedded logic are just fine for small projects with two or three programmers. In large scale (*scalable*) projects however, and regardless of the database, they're avoided because of performance penalties and becasue they violate designs rules for the abstraction of data and business logic (as well as portability -the abailty to migrate from one database to another).

The difference is that the big boys use multi-tiered architectures and dont put business rules in their database.

Credit to the MySQL designers for their focused thinking.

An (pending) article on this topic: http://www.onnet.cc/articles/19b3f4ac.htm

I think that the implementation of Stored Procedures should allow a stored procedure to return (multiple) results from (multiple) select statements (like MS SQL Server 7/2000), because it allows a client side to treat a database Stored Procedure as a data layer API. This separates a job of a database people who would prefer to have all the SQL code in one place (in database) from the rest of the data consumers, who need to know what parameters to pass to which procedure and what columns from the resulting data to work with, without getting into the actual database structure/design.

Ah, there are some here doing projects with more than 3 programmers. Students of Ivar Jacobson I suppose, who loves to talk about projects with 1000 programmers involved.

3 or 1000, interfacing to Databases is a pain, a real pain. Changing the code, when some chang is done to the db to enhance performance is a pain. STPs aleviates from this pain a significant bit.

I agree absolutely with those, who say, STPs are not for modelling Business Logic. But STPs are very good to make the interface to the db more narrow.

Some argue, that all what I am saying is not significant, when the datamodell and application is well thought through. Well that is true, but in this case, where I dont need the flexibility to guard agains my own mistakes, I wouldnt use an SQL databse aniway. In this case an oo dabase will be faster than the fastest MySQL.

I agree. SPs are not a cure-all for poorly implemented business rules. The real place that should guarantee that the application perfoms in 100% compliance with business rules is QA. The QA department doesn't care whether the logic is implemented in the database or in the application. Their primary concern is the application returning the right results quickly and reliably.

My Solution To "Stored Procedures"

I have been working with Microsoft SQL Server since it's inception in 1992. Yes, many people are not aware that MS SQL Server has been around that long... but it has.

I only bring up the point of my time spent with Microsoft SQL Server not to "impress" you with what could be conceived as my programming ability, but simply to draw a time-line that demonstrates my "time spent" with stored procedure technologies.

As many programmers out there who have grappled with stored procedures in the MS world. I am certain that most would agree in a large "corporate" environment where accountability is paramount. Stored procedures are DEFINITELY the way to go.

Unfortunately, many databases do not allow for the internal storage of SQL in the format of "stored procedures", so.... when all else fails, create your own.


Here's what I did to fix the problem of "stored procedures" with many various databases that I have used in the past including MySQL, Access, MS SQL Server (older versions), DBase, FoxPro.. etc.

In this example, I am going to use Visual Basic as the programming tool of choice. You should be able to easily convert the theories discussed here into your programming language of choice.

First, I created a table in my database called "StoredProcedures"

The Table Structure is as follows:
NOTE: I only offer the structure as a "guideline". Please do not send me emails telling me all about how my table structure does not allow for "this" and "that". Your individual programming techniques are not the same as mine, or the guy sitting next to you. Use your own judgment as to what you deem "relevant" as I have.

ProcedureName : VarChar(255) : Primary Key
ProcedureText : LongText (mysql)
DateAdded : DateTime


Now, I begin adding my stored procs.
Below is a typical example of a stored procedure

ProcedureName: dp_CIO_chatterdiary_del_diaryid
NOTE: I follow the below construct when creating stored procedures.

dp_ (signifies a "database procedure")
NOTE: Many developers use "sp_" (MS SQL Stored Proc) as the stored procedure "preamble". I have discovered that Microsoft SQL Server treats any stored procedure with the preamble of "sp_" as a "System" level stored procedure. If you have any quantity of stored procedures above the number of say 5 (of course I realize most serious applications have hundreds even thousands) then you will begin to notice a serious impact to MS SQL Server performance, hence the change to "dp_".


_CHD (signifies the portion of the program that utilizes this procedure)
NOTE: In this example this program was a "chat" program with some specialized functionalities for storing information about chatters. One of the primary functions of the program was a "chat diary". I used "CHD" to signify that this stored procedure was used by the "Chat Diary" portion of the application.

Many have asked why I do this. In an enterprise application, it is imperative that you isolate your code from "code creep" and other issues that would cause a problem with other sections of your application due to changes made. For instance, a problem that plagues most developers is making a change to one portion of an application (seemingly innocent change) and suddenly another portion of the application starts breaking down. One of the primary culprits can be database changes, and/or changes to stored procedures. With this naming convention, you can know 100% that this stored procedure is ONLY used by the "Chat Diary" portion of the application, and any changes made to it will ONLY affect that portion of the application.

Okay, I know what you're thinking. "WHAT ABOUT CODE REUSABILITY?????" And.... you are absolutely right. This naming convention totally negates "code reusability" where it applies to stored procedures. But let me ask you this question. Would you rather be able to use the same stored procedure for 100 subroutines scattered throughout your application, and when a change is made only 40% of them break down, and you don’t even know it because that section of the application is only used when someone prints Report X? OR.... Would you rather know with 100% certainty that this stored procedure only touches parts of the application that are relegated to the CHAT DIARY section, and you can thoroughly test EVERY part of the chat diary to ensure that the stored proc is working properly?

For those of you who have been coding for very long. You also understand that in today's modern programming languages COM and COM+ objects are the primary solution to code "reusability" and thus, this naming convention only further solidifies the fact that "code reusability" from a stored procedure standpoint is not only less important, but is down-right criminal, and would only lead to problems discussed earlier in this note.


_chatterdiary (table being modified)

_del (function being performed on table)
NOTE: Other functions are _ins,_upd,_sel. This is only to let us know what functions are being utilized as the primary function of this stored procedure. Obviously (especially in MS SQL Server) there could be a myriad of functionalities within one stored procedure.


_diaryid (primary selection key)
NOTE: This is nothing more than just to alert the developer what the primary information selection key is for the provided functionality.





ProcedureText:
Delete From
ChatterDiary
Where
DiaryID = @DiaryID


Obviously, if you've been working with SQL for more than a day, you are painfully familiar with the command structure here. You may also think you know what the @DiaryID is. But, don’t draw any fore-gone conclusions. I am about to share with you what that REALLY is.

Many of you are familiar with "replaceable parameters". Normally, in a stored procedure the @VARIABLENAME is a parameter used as a variable holding place for data. What I have done is to use these in a different way for variable replacement within my applications.


Now, let’s get into the visual basic application just a little.

Let’s say that you have a place in your application that you want to delete a diary entry from your database. The code (pseudocode) might look something like this.

NOTE: DO NOT SEND ME EMAILS exclaiming that I have a typo, code violation, or that you have copied and pasted this code into visual basic and it didn’t work. This code is here ONLY to convey a “concept” not the actual code required to make this work. The underlying code to make this concept work is literally a couple hundred lines of code.


Dictionary Objects:
Microsoft has a component known as a “Dictionary” object. This object allows you to store information in it by “key” and retrieve it by “key”. If you want to play around with it. You can use it in Visual Basic by using the following code:

Dim objDatabaseDict as Object
ObjDatabaseDict = CreateObject(“Scripting.Dictionary”)

Usage:
Putting data into the dictionary object:
objDatabaseDict(“FirstName”) = “Greg”

Getting Data FROM the dictionary object:
MsgBox objDatabaseDict(“FirstName”)

Sub Delete_DiaryEntry (ByVal DiaryID as Long)

Let’s Say DiaryID = 10023
ObjDatabaseDict(“DiaryID”) = DiaryID
Result = adofunctions_StoredProcedureCall(rsResultSet,”dp_CIO_chatterdiary_del_diaryid”,objDatabaseDict)


End Sub



Sub adoFunctions_StoredProcedureCall(byRef rsResultSet,StoredProcName,objDict)

Request the "ProcedureText" info from the database using the "StoredProcName".
"Select ProcedureText From StoredProcedures Where ProcedureName = 'dp_CIO_chatterdiary_del_diaryid'"

The Dictionary object allows for "iteration" through its "keys". You would simply iterate through the keys looking for a key that matched the replaceable parameter in your stored proc, and replacing them accordingly.

Once you have replaced the info in your ProcedureText, the proc would look something like this...

Delete From
ChatterDiary
Where
DiaryID = 10023

Now, simply create your ADO connection, and execute this statement.



End Sub




Hopefully this helps someone out there who is grappling with using MySQL in an enterprise environment with no "stored procedure" support.

Sincerely,
Greg Oglethorpe

All comments downgrading the importance of implementing stored procedures and triggers in mysql, is baseless and completely out of touch with today modern application development. If someone consider those facilities unimportant or useless, he or she will always be able to avoid using them in their daily work as software developers, but do not try to influence others with your limited and particularly restricted opinions.

an idea would be to allow the user to choose the type of engine to be used for the stored procedures.
The proposed engines would be php or Perl and maybe even java.

I know this may sound peculiar. but if we use a subset of pl/sql then for certain other triggers we may have to use pl/c etc.

so consider an environment where the database can provide data to client side using PHP and the database itself can process it's triggers using PHP or Perl or some other designated engine.

an Open concept for triggers...

Just a thought.
raja

A trigger should be executed only on the dbserver to better guarantee that such a synchronous event would actually complete. This is mainly due to the fact that a trigger is considered only part of a transaction. A stored procedure called by a client program is usually thought of as an entire transaction.

A stored procedure should have the option to execute on or off the dbserver but the default should be to execute on the dbserver. The variables that drive execution on or off the dbserver are network chattiness and dbserver CPU consumption. If a procedure spends most of its time in the execution of SQL or in network roundtrips, then it should execute on the dbserver. If, however, the procedure spends most of its time executing non-SQL statements (e.g., if, expression evaluation, assignment), then it should execute off the dbserver. The reason the default should be on the dbserver is that in my 17 years nearly all stored procedures that I've seen have spent most of their time executing SQL. This applies even to procedures that have had their SQL optimized (and especially so for those that haven't).

I decided to come and see how far MySQL has moved on since I last looked. Not very far from the looks of it. Not supporting any form of processing language within the database makes the database wholly unsuitable for any serious business applications - IMHO.

I'm fed-up of workarounds in computing - I've seen hundreds and have had to code a lot of them. Is it not about time MySQL grew up and started to truly compete with the main players (IBM, Oracle and Microsoft). All these kids that think coz they use MySQL they are DBA's. We all know that is bolx. Why the hell would I want to create a massive work-around to enable me to utilise MySQL when for a few quid I could utilise the unmatched functionality and superior performance of SQL Server. If any one wants to "'av a go" over this statement I suggest they walk in my shoes for 4 years first. I used to scorn at SS 6.5 until I read the white papers of SS 7.0. Being open minded I was astounded. Anyone who has not seen DTS's or the OLAP Server in full operation and not gone WOW! obviously live a very sheltered, slipper wearing, pipe smoking life. I get the hee-bee-gee-bees thinking about some of the data analysis problems I have been able to solve using combinations of "Views" and "Stored Procedures". Not forgetting "Functions". Everyone seems a bit frightened to mention those, which are available within SS 2000. I wonder how many of you MySQL lovers have ever actually spent anytime working on large corporate databases with hundreds or even thousands of tables - using proper tools. You cannot allow the developers to have SQL code embedded in their application logic. It is a recipie for disaster. You must have some form of segregation. As much as I hate DBA's myself, they have vital role of ensuring that we all utilise sound methods of access to the data. I dont agree that the DBA should design the database, because in general, they are not living in the real world and will try to be too normalised. In most cases access is best achieved through use of standard methods. ie "Stored Procedures". I prefer my DBA's to concentrate on Indexs, Back-ups, Transaction Logs and Performance and leave the application stuff to the people that talk to the clients and users of the applications.

You might think I am a bit MS leaning. Well I am at the moment. I used to lean towards Oracle, especially after working on Sybase. However, MS SS7.0 blew Oracle out of the water. Oracle are desparate to catch up with their latest 9i but they have a shaky foundation. Just remember they bought Express IRI as their entry into the OLAP arena - Great business decision that was - NOT!

Anyway, when MySQL does all that SQL Server does and a whole lot more, I will of course be interested. Until then I will be steering clear to ensure I do not have to waste my time cobbling applications together writing poxy work-arounds based on the limited functionality available in the DB.

Well
My first impression was “I cannot believe it”
I come from a Microsoft background
And I do believe in the N-Tier architecture
The idea is that I am against putting the database manipulation code in Component
An Insert, delete, or update may be straight forward and I am not talking about the performance
But what I am talking about is specific values
For example, I would like to do some calculations and return the result back
What if I want to change the logic
Should I always do it at the component level???
I know that this may be reasonable
And maybe it is a matter that I am not used to this
But actually I do believe that the least to be with MySQL are the stored procedures
I do believe that it is not only a fast engine it should be
A fast engine that returns results I don’t need because I don’t have stored procedures
And I have to filter the result with my own code
Then the application will be slow
It is not the engine but it is the over all performance
Adding up the network traffic and so on
I do believe that we will be needing Stored procedures support very soon.
Other than that I don’t think that we can say that we have the freedom to have an N-Tier architecture

Thanks to all

Let me use DLL functions as triggers. Just designe a simple interface to call DLL functions and pass few parameters describing, which table and which row was modified and in what way. On Unix this might be dynamically loaded modules ".so". You might also include a connection handle as a parameter. This will rock.

Yakov Sudeikin
exebook.com

i agree that stored procedures, triggers and views should be implemented. they definately should be in a seperate layer though. my mindset is let the database be the database and those functions begin to move the database in the application layer. if mysql can stay out of the oracle mindset, where they try to justify an expensive db by making it a replacement for application level logic or the application level itself thing should be just fine.

add all the application layer things you want to mysql, just make sure those of us that want to run the db as a db don't have to run into it's bulk.

MS SQL 7 was my first commercial relational database after supporting an Open VMS DB for 5 years (my hair was brown when I started and is now salted). I felt like I traded my VW Bug for a Mercedes because when handled correctly, stored proc's and triggers in MS SQL can greatly improve performance, development time and scalability. Triggers are essential for data integrity. To leave business rules in the hands of programmers at the application level is a recipe for disaster as I found with VMS.

I recently took on a development project for an organization that adopted MySql as the DBMS for their website and was very disappointed when I found that stored procedures and triggers were not supported.

Unfortunately I had to come up with a workaround for my project since I didn't have the power to change the DBMS. I ended up creating a table of SQL statements and a DbFunction Perl module. The SQL is stored in the database with placeholders for variable parameters. My DbFunction module handles db connections and contains a subroutine that will return any sql statement by name and replace placeholders with scalars provided. So now the only sql I have in my application layer is a single statement that retreives the needed sql for the moment. This model should allow an easy transition to real stored proc's if they do come in a future release.

In my experience (6 years as a contract database programmer in various City Financial organisations), Stored procs for serious application development is a must, but triggers are less important, but still definitely nice to have, and can be very nice to have in certain situations.

The pros of Stored procs include:

a) speed,
b) modularity (u want to reuse the logic? Just make a call to the stored proc, rather than copying and pasting big SQL statements)
c) the logic is kept out of the hands of the app developers, and is done up-front in SQL by those that 1) understand the business data, and 2) understand SQL and databases,
d) A different front-end can be plugged into the database, and u know that the rules will still apply (without having to re-write them).

Some possible cons for stored procs:

a) the number of server-side objects increase,
b) the load on the server increases (conversely, the load is transferred from the client to the server, which is normally a good thing)
c) Additional effort can be required to maintain the source code, and to ensure that changes don't break things.

All in all, the bigger the project, the more I rely on stored procs. Twice already (this is the second time), I have come to the current production release of MySQL to see if stored procs have been implemented (as I consider them critial), and twice, I have been forced to go elsewhere in order to get what I consider requisite functionality. I'll be back again though. No doubt.

I know it has been alluded to in prior comments, but I would like to emphasize that in most database situations, access to the database itself is not restricted to just the main application code. So, any independently run command line query can negatively impact the integrity of the database, create orphan records, etc. This implies that a central server function needs to be available and utilized to control access at the server level, because the main application is only a part of the full access used to the database.

It is unfortunate that integral database capability to enforce relational and content constraints are most often not comprehensively useable in large, high transaction volume database applications because they kill needed performance levels. Often these are touted as functional capabilities in marketing the product, but are not implementable in real life situations.

Ideally, if the database server were capable, one would only need to define comprehensive relational and content rules within the database in a set of metadata tables, allowing the database server to enforce these rules implicitly, and not require stored procedures and triggers as custom developed routines for a poor and often unmanageable substitute.

MS recommended at one time in their technical papers for enforcing relational and data integrity in a large SQL Server database, that actual server check constraints and relationships not be used, but that triggers and stored procedures be utilized in order to achieve desired performance levels.

Ideally, effective and efficient implementation of central metadata rules in the database server could eliminate most of the custom code we now find in Stored procedures and triggers. Haven't you noticed in large enterprise implementations that most of the stored procedures and triggers are essentially identical process flow copies with minor changes to tailor each to specific table/column names?

Add your own comment.