Извеждане на дърво от 1 таблица / Компютри и Интернет / Бази данни

1.7.4.4 Stored Procedures and Triggers

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 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.

A trigger is a stored procedure 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 transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

The planned update language will be able to handle stored procedures. Our aim is to have stored procedures implemented in MySQL Server around version 5.0. We are also looking at triggers.

--------------------------------------------------------------------------------

Comments:
Ryan Hatch: Triggers would really help me implement a complete database structure where the domain of data is tested by the database. Right now I have to program every constraint about the data into my application - which is very inefficient.

Mike B: 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.

Pete Morgan: Views ie derived tables and Stored procedures would be absolutely fantastic. Views first as this means complex joined queries can be queried as a "virtual table". This means my team of develpers who use Microshaft SQL will feel at home ;-)

Andrew Moore: I currently manage a team of DBA's who are responsible for around 28 production databases on an Win2000 Server environment utilizing SQL Server 2000. I would like to comment on MySQL regarding a few items. It will be nice when the latest version is stable that supports stored procedures, triggers and (views?). These items are great for implementing dbms specific performance issues. MySQL has performed as well if not better than most dbms I have come across. I currently have it running on a system that supports large numbers of transactions across the net and have literally had hardly any problems for well over two years. Anyway, good job people, will continue to utilize your product...

Pete Belford: Triggers???? Your nuts if you want triggers. It will probably slow down the database; and is a poor programming practice. As one person put it, triggers are like throwing ping pong balls into a room full of mouse traps.

Brad Neufeld: 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

Tim Williams: I'd like to add my comments in support of triggers. However, I'd also like to second the comments made here about avoiding creating an entirely proporietary language to implement them. Of course you don't need to support every little feature of a pre-existing language, but having its features supported or not supported is better than having an entirely different implementation.

Adrian Flanagan: 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.

Sean Rhone: So, if no Stored Procedures what is the eqiualant?

James Drabb: Please add stored procedures and sub-queries. These are the two most important features IMHO. I would stay away from triggers. I think they would only slow down mySQL. mySQL's greatest strength is its RAW speed! Don't muck it up with bloated features. Any compilcated DB can be implemented without triggers. However, without sub-queires it can be a little bit of a pain. Stored procedures can only help make mySQL even faster by having a pre-compiled query ready to go. mySQL should stick to speed and the ability to handle large amounts of data. Triggers are for maintaining business rules. MySQL is meant to handle data, not business rules. Let the logic for business rules stay within the program. Being a programmer I don't want to have to rely on a dba to get my logic correct. I also do not want to run a query and not know every thing that is affected by that query which can happen with triggers. Say yes to stored procedures and sub-queries, and not to triggers : )

Jim McNeely: 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.