Wikibooks: MySQL/Stored Programs

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/MySQL/Stored_Programs

MySQL supports some procedural extensions to SQL. By using them you can manage the control flow create loops and use cursors. These features allow you to create stored programs which may be of 3 kinds Triggers programs which are triggered before / after a certain event involves a table (DELETE INSERT UPDATE) Events programs which are executed regularly after some time intervals Stored Procedures programs which can be called via the CALL SQL command. MySQL future versions will support stored program written in other languages not only SQL. You will have the ability to manage new languages as PLUGINs. Also the stored procedures will be compiled into C code and thus they will be faster. = Triggers = = Managing Triggers = Triggers were added in MySQL 5.0.2. They work on persistent tables but can t be associated with TEMPORARY tables. = CREATE TRIGGER = To create a new trigger CREATE TRIGGER `delete old` AFTER INSERT ON `articles` FOR EACH ROW BEGIN DELETE FROM `articles` ORDER BY `id` ASC LIMIT 1 END This example trigger defines a stored program (which is the simple DELETE statement) called `delete old`. It s automatically fired when a new record is INSERTed into `articles`. It s called after the INSERT not before. If a single INSERT adds more than one row to the table `delete old` is called more than once. The idea is simple when a new record is created the oldest record is DELETEd. A trigger may be executed BEFORE or AFTER a certain SQL statement. This is important because a trigger may execute one or more statements which activate other triggers so it may be important to decide their time order to ensure the database s integrity. The statement which fires the trigger must be a basic DML command INSERT which includes LOAD DATA and REPLACE DELETE which includes REPLACE but not TRUNCATE UPDATE A special case is INSERT . ON DUPLICATE KEY UPDATE. If the INSERT is executed both BEFORE INSERT and AFTER INSERT are executed. If the INSERT is not executed and thus an UPDATE is executed instead the order of events is the ...