1.9.4.4 Хранимые процедуры и триггеры

Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций.

Триггер представляет собой хранимую процедуру, которая активизируется при наступлении определенного события. Например, можно задать хранимую процедуру, которая срабатывает каждый раз при удалении записи из транзакционной таблицы - таким образом обеспечивается автоматическое удаление соответствующего заказчика из таблицы заказчиков, когда все его транзакции удаляются.

Возможность работы с хранимыми процедурами будет обеспечивать планируемый язык обновлений. Наша цель - ввести хранимые процедуры приблизительно в версию сервера MySQL 5.0. Мы работаем также и над триггерами.

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.

Add your own comment.