MySQL/Programming

TODO: alles in nowiki-tags --- mach et schoen!!!

Programming with MySQL In this lesson of the MySQL tutorial, you will learn... 1. How to write functions in MySQL. 2. How to write Stored Procedures. 3. How to write Triggers. This lesson focuses on the programming aspects of MySQL covering traditional control-structure based code artifacts in MySQL to supplement the regular SQL-based functionality. Stored procedures Stored procedures (SPs) are an important addition to MySQL 5.0 onwards. These are custom programming scripts with embedded SQL statements that are stored in a compiled form and executed directly by the MySQL server, generally with no external programming interface requirements. SPs allow store logic rules on the database. Why Stored Procedures? * Faster Execution:Reduced need for data transfer back and forth between a program and the database server. Note: The use of SPs does not guarantee improved speed as a lot depends on the code inside of the SP. * Reduced code redundancy: There generallt are similar code segments in each application for managing data such as templated inserts and updates and can be moved into a database-side SP. * Maintenance: If there are changes in the underlying database schema, code changes can be localized to a few SPs. * Better database security: In security-sensitive applications, such as banking, direct access to tables by user programs is a problem and with SPs, data access can be monitored, and logged if necessary. Also, one can impose centralized security rules in database procedures. Drawbacks for SPs * Lack of Portability: SPs are hardly portable from one database system to another, requiring significant coding and testing efforts. * DB Server Load: Intensive use of SPs burdens the MySQL server programs, which could be a negative. * Limited Programming: SP programming is generally not as rich as conventional development platforms such as Java or PHP. This may also lead to sub-optimal code hurting performance somewhat. Note:Future releases of MySQL will likely offer an interface that permits creating SP code using external programming languages so SPs can use all of the features of languages such as Java. Routines consist primarily of a collection of regular SQL commands. In addition, you can create loops and conditional branches, and cursors to run through all the records in a table. There are two types of routines: procedures and functions. The following table summarizes the most important differences between the two. Differences Between Procedures and Functions Procedures Functions Invoking Use CALL Use in any SQL command, such as SELECT, UPDATE Return Values Can return one or more SELECT results and out parameters Returns a single value via RETURN matching data type of the return value specified in the declaration with RETURNS. Parameters Value (input) and reference (output) parameters (IN, OUT, INOUT) Only value (input) parameters allowed, needs no tagging such as IN,etc. Commands allowed in code All DDL and DML SQL commands supported No support for commands that access tables. Call other routines May invoke other procedures and/or functions Only functions can be called but not procedures. SP Implementation The SQL 2003 standard: The syntax of SPs in MySQL 5.0 is fortunately based on a standard, namely SQL:2003. Though SPs are more or less incompatible in different database servers. Internal storage of SPs: MySQL stores SPs in the table mysql.proc. In the columns of this table are stored the name of the underlying database, the name and type (PROCEDURE or FUNCTION) of the SP, the parameters, the actual code, and various other attributes. Note: Every SP and function is associated with a particular database. Creating Routines New Routines are created using CREATE FUNCTION or CREATE PROCEDURE. You need the privilege Create Routine in order to be able to execute these commands. The following lines summarize the syntax for these commands: CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode CREATE PROCEDURE name ([parameterlist]) [options] sqlcode It is allowed for a function and a procedure to have the same name, as they have different calling mechanisms. Code Sample: Programming/Demos/Function-Reduce.sql DELIMITER ; DROP FUNCTION IF EXISTS reduce; DELIMITER $$ CREATE FUNCTION reduce(str VARCHAR(255), len INT) RETURNS VARCHAR(255) BEGIN IF ISNULL(str) THEN RETURN NULL; END IF; IF len = 0 OR CHAR_LENGTH( str ) = 0 OR CHAR_LENGTH( str ) <= len THEN RETURN str; END IF; IF len < 15 THEN RETURN LEFT( str, len ); ELSE RETURN CONCAT( LEFT( str, len - 10 ), ' ___ ', RIGHT( str, 5 ) ); END IF; END$$ DELIMITER ; Code Explanation Change delimiting to $$ to allow for semicolon use inside of function: delimiter $$ Two parameters are passed to the function reduce(): a string str and an integer len representing the maximum return length. The return type is a string (data type VARCHAR(255)). If len is less than 15, then LEFT len characters are extracted from the string passed. If len is greater than or equal to 15, then oversized strings are shortened so that the resultant string contains the first len - 10 and the last 5 characters, with the string " ___ " in between. Revert back delimiting to semicolon : delimiter ; Specification Options In the definition, the following options can be specified: * LANGUAGE SQL: The only permissible setting for the option LANGUAGE is currently SQL. This setting holds by default. Future versions of MySQL will likely offer the option of defining SPs in other programming languages. * [NOT] DETERMINISTIC: A Routine is considered deterministic when it always returns the same result with the same parameters. (Routines whose result depends on a database table are thus not deterministic.) * By default, SPs are nondeterministic. However, deterministic SPs can be executed with greater efficiency. (For example, it is possible to store the result of an SP for particular parameters in a cache.) At the moment, however, the MySQL DETERMINISTIC option is ignored. * SQL SECURITY DEFINER or INVOKER: The SQL SECURITY mode specifies the access privileges with which an SP is to be executed. Details will be given later in the lesson. * COMMENT 'text': The comment text is stored together with the SP. Deleting Routines Use usual DROP commands to delete routines: DROP FUNCTION [IF EXISTS] name DROP PROCEDURE [IF EXISTS] name Changing Routines ALTER FUNCTION/PROCEDURE name [NAME newname] [SQL SECURITY DEFINER/INVOKER] [COMMENT 'newcomment'] MySQL currently (version 5.0.3) offers no way of changing the code of an existing SP. If that is what you would like to do, then you must first delete the SP with DROP, and then create it anew with CREATE. Listing Existing Routines The two commands SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS return a list of all defined procedures and functions. Using LIKE pattern you can limit the output to those procedures/functions whose names correspond to a given search pattern. In any case, you will obtain the SPs of all databases (not only the current database): Code Sample: Programming/Demos/Show-Status.sql SHOW FUNCTION STATUS; SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS LIKE 'repeat%'; SHOW PROCEDURE STATUS LIKE 'film%'; Code Explanation Use SHOW STATUS for functions and procedures. LIKE is used to filter the list shown. More complete information is provided by the table information_schema.routines. It contains all data of the table mysql.proc, though in part it employs different column names: Code Sample: Programming/Demos/Show-Routines.sql SELECT routine_name, routine_type, created FROM information_schema.routines WHERE routine_schema='sakila'; Code Explanation We will SELECT information from the routines table in information_schema. Determining the Code of an SP If you know the name of an SP, you can access its code. Execute the command SHOW CREATE FUNCTION/PROCEDURE name: Code Sample: Programming/Demos/Show-Create-Function.sql SHOW CREATE FUNCTION reduce; Code Explanation Use SHOW CREATE statement. Programming Routines - Inside the Routines Encapsulation of Commands (BEGIN-END) Every procedure or function that consists of more than one SQL command must be introduced with handler and cursor.) Within the BEGIN-END block a particular order must be adhered to: BEGIN DECLARE variables; DECLARE cursors; DECLARE conditions; DECLARE handler; other SQL commands; END; Before the BEGIN, an optional label can be given. The same name must then appear after the END. A Named block may be exited early with LEAVE: blockname: BEGIN commands; IF condition THEN LEAVE blockname; END IF; further commands; END blockname; Semicolons separate individual commands within an SP. Using Variables There are two types of variables to consider in MySQL programming: * Ordinary SQL variables: Such variables are prefixed with the @ character. These variables can be used in SPs just as they are in ordinary SQL commands. They maintain their contents until the termination of the connection to the MySQL server. * Local variables and parameters: These variables are used without the @ character. They must be declared with DECLARE before they are used. The content of a local variable is lost as soon as the procedure or function terminates. Variables are local within the BEGIN-END group in which they are defined. This means that within a procedure several like-named variables can coexist within different levels of definition as shown int following example). To avoid side effects, local variables are generally preferable. The declaration of local variables must take place within a BEGIN-END group and before other commands in the group. The syntax of variable declaration looks like this: DECLARE varname1, varname2, ... datatype [DEFAULT value]; As you can see, you must provide the data type for all local variables. Local variables contain NULL by default, unless you initialize them with another value. Warning: Be very careful not to give your variables names that coincide with those of columns or tables that you use in your SP. It is allowed syntactically, but it frequently leads to hard-to-detect errors (which can be revealed, for example, in that a variable, despite an assignment otherwise, always contains NULL). Code Sample: Programming/Demos/Create-Proc-3Blocks.sql DELIMITER ; DROP PROCEDURE IF EXISTS `3blocks`; DELIMITER $$ CREATE PROCEDURE `3blocks`(n INT) BEGIN DECLARE newn INT DEFAULT n; BEGIN DECLARE newn INT DEFAULT n * 2; IF TRUE THEN BEGIN DECLARE newn INT DEFAULT n * 3; SELECT n 'Orig', 3 'Run', newn 'New Factor'; END; END IF; SELECT n 'Orig', 2 'Run', newn 'New Factor'; END; SELECT n 'Orig', 1 'Run', newn 'New Factor'; END$$ DELIMITER ; CALL `3blocks`(10); Code Explanation There are three variables named same as newn. Variables are declared in three levels of the code and are independent of one another. The procedure returns three results, multiplying given parameters by 1, 2 and 3. Variable Assignment For variable assignments, use either SET or SELECT INTO. The latter is a variant of SELECT, in which the command ends with INTO varname.This variant is allowed only for a singleton SELECTs that return a single row. In functions, only SET can be used, since there the use of SELECT and various other SQL commands is not allowed. Code Sample: Programming/Demos/Assign-Vars.sql DELIMITER ; DROP PROCEDURE IF EXISTS `assign_vars`; DELIMITER $$ CREATE PROCEDURE `assign_vars`(n INT) BEGIN DECLARE varlength SMALLINT; DECLARE pgcount SMALLINT; DECLARE var1 SMALLINT; DECLARE var2 SMALLINT; DECLARE var3 SMALLINT; SET var1 = n * 5, var2 = n * 10, var3 = n * 15; SELECT @var := CONCAT_WS( ',', var1, var2, var3 ); SELECT CHAR_LENGTH(@var) INTO varlength; SELECT COUNT(film_id) FROM film WHERE rating = 'PG' INTO pgcount; SELECT @var, varlength 'Length of @var', pgcount 'PG Rated Film Count'; SELECT title, rating FROM film WHERE film_id = 101 INTO @vtitle, @vrating; SELECT 101 'Film ID', @vtitle 'Title', @vrating 'Rated As'; END$$ DELIMITER ; CALL `assign_vars`(10); Code Explanation Different variables are assigned different values. Some are declared variables and others are SELECT-style variables. General Programming Syntax Rules These are the most important syntax rules for programming routines: * Semicolon:The commands in routines are separated by semicolons. Even control structures (branches and loops) must be terminated with a semicolon. * BEGIN-END: Several commands that do not fall between key words (for example, between THEN and END IF) must be placed between BEGIN and END. The code block of every routine with multiple statements must start with a BEGIN and terminated by END. * Spacing: White space is for readability purposes. * Variables: Local (internal) variables and parameters are used without @ prefix. The ordinary SQL variables are used with the @ prefix. * Case distinction: The definition and invocation of routines are case insensitive. * A function and a procedure can have the same name. * Special characters:MySQL allows a lot of flexibility for special characters in routine names. ,but external programming and administrative tools may cause problems. * Comments:Comments start with a double hyphen (-) and run to the end of line. Invoking Procedures Procedures must be called with CALL. You can return as result a table (as with a SELECT command). t is impossible to link procedures to SQL commands in any other way. The following examples use the procedures get_title and half: Code Sample: Programming/Demos/Proc-Case.sql DROP PROCEDURE IF EXISTS oper; DELIMITER $$ CREATE PROCEDURE oper(IN a FLOAT, INOUT b FLOAT, IN oper VARCHAR(1)) BEGIN CASE oper WHEN '+' THEN SET b := a + b; WHEN '-' THEN SET b := a - b; WHEN '*' THEN SET b := a * b; WHEN '/' THEN SET b := a / b; WHEN '%' THEN SET b := a % b; WHEN '^' THEN SET b := POW(a, b); ELSE SET b := a; END CASE; SELECT b; END$$ DELIMITER ; SET @b := 5; CALL oper( 15, @b, '+' ); SELECT @b; Code Explanation A Case statement is used to use operators. Three parameters are supplied - two floats and an operator string. The second float is an out parameter and comes initialized with the result of the operation. Tip: To execute an SP in another database, prefix the database name as in CALL dbname.spname(). Parameters to Procedures Procedures are created by the command CREATE PROCEDURE. A parameter list is optional. Note, however, that you must provide a pair of parentheses even if there are no parameters. CREATE PROCEDURE name ([parameterlist]) [options] sqlcode If there is more than one parameter, they must be separated by commas. Each parameter is specified as follows: [IN or OUT or INOUT] parametername datatype The keywords IN, OUT, and INOUT determine whether the parameter is to be used only for input, only for output, or for data transport in both directions. (The default is IN.) All data types from MySQL are allowed, such as INT and VARCHAR(n) and DOUBLE. However, in contrast to the definition of table columns, it is impossible to provide additional attributes to the data type such as NULL and NOT NULL. MySQL currently does not attempt to do type checking when it passes parameters, but that may change in future versions. Be careful to give your parameters names that differ from those of your columns and tables. Otherwise, ambiguity can enter into the formulation of SQL commands in procedure code. Function Parameters The CREATE command for creating new functions looks very much like that for procedures: Syntax CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode A significant difference, however, is that functions do not support reference parameters. For this reason, the keywords IN, OUT, and INOUT are not permitted in the parameter list. Note: We execute the script using source filename as usual, but since source requires the commands to be separated by semicolons, and it does not accept the command delimiter, you may have some trouble running a script. Results of Procedures (SELECT) Unlike functions, procedures do not return a single value. In procedures, however, one may use ordinary SELECT commands. It is even allowed for a procedure to execute several SELECT commands in sequence. The procedure then returns several result tables. However, only those programming languages that support the MULTI_RESULT mode are capable of evaluating several results. If you are working with PHP, you must use the method multi_query of the mysqli interface. Function Results Functions can return a value with the command RETURN, which also terminates the function execution. RETURN can be used only in functions, not in procedures. The data type of the return value must be specified in the parameter list with RETURNS. Procedures and Functions differ in a number of details that will be discussed in this lesson. Invoking Functions Functions, like the predefined SQL functions, are integrated into ordinary SQL commands. For example, the reduce function from can be called as: Code Sample: Programming/Demos/Use-Function-Reduce.sql SELECT reduce("abcdefghijklmnopqrstuvwxyz", 15); SELECT title, reduce(description, 25) 'ShortDesc' FROM film_detail WHERE rating = 'PG' AND category_name = 'Children' ORDER BY title; Code Explanation The first usage simply "reduce"s a constant string. The second usage "reduce"s a database column description from film_detail. IF-THEN-ELSE Branching The SQL syntax for IF branching looks like this: Syntax IF condition THEN commands; [ELSE IF condition THEN commands;] [ELSE commands;] END IF; The use of blocks marked with BEGIN and END within a control structure is not necessary. The condition can be formulated as with WHERE or HAVING in SELECT queries. Tip: Instead of the IF structure presented here, in simple cases you may use the IF() function. The following function is an example of several IF conditions. The function returns a string enclosed with given strings. Code Sample: Programming/Demos/Function-Enclose.sql DELIMITER ; DROP FUNCTION IF EXISTS enclose; DELIMITER $$ CREATE FUNCTION enclose(str VARCHAR(255), leftstr VARCHAR(3), rightstr VARCHAR(3) ) RETURNS VARCHAR(255) BEGIN DECLARE returnstr VARCHAR(255) DEFAULT ''; IF ISNULL(str) THEN RETURN NULL; END IF; IF leftstr IS NULL OR rightstr IS NULL OR CHAR_LENGTH( str ) = 0 THEN RETURN str; END IF; IF LEFT( str, CHAR_LENGTH( leftstr ) ) != leftstr THEN SET returnstr = concat( leftstr, str ); END IF; IF RIGHT( str, CHAR_LENGTH( rightstr ) ) != rightstr THEN SET returnstr = concat( str, rightstr ); END IF; IF returnstr = '' THEN SET returnstr = str; END IF; RETURN returnstr; END$$ DELIMITER ; SELECT enclose( 'abc', '[', ']' ); SELECT enclose( '(abc', '(', ')' ); SELECT enclose( 'abc)', '(', ')' ); SELECT enclose( '(abc)', '(', ')' ); SELECT enclose( '[abc)', '(', ')' ); Code Explanation Three parameters is passed to the function str: string to enlose, left enclosure, right enclosure. The return type is a string (data type VARCHAR(255)). Some preliminary parameter validation is performed. The function checks IF the string already has the given left or right enclosures, if not present, the enclosures are added. Branching Using CASE CASE is a syntactic variant of IF that is particularly useful when all the branch decisions depend on the value of a single expression. CASE expression WHEN value1 THEN commands; [WHEN value2 THEN commands;] [ELSE commands;] END CASE; REPEAT-UNTIL Loop The code block between REPEAT and UNTIL is executed until the condition becomes true. The condition is evaluated only at the end of the loop body, at least one pass through the loop. The loop can be given an optional label. The same loop name must then appear at the end of the loop. Naming a loop can be useful if you exit the loop prematurely with LEAVE or wish to repeat a loop iteration with ITERATE. [loopname:] REPEAT commands; UNTIL condition END REPEAT [loopname]; The following function is an example of a REPEAT-UNTIL loop. The function returns a string containing the + character n times. Code Sample: Programming/Demos/Function-Repeat.sql DELIMITER ; DROP FUNCTION IF EXISTS repeat_plus; DELIMITER $$ CREATE FUNCTION repeat_plus(n INT) RETURNS VARCHAR(255) BEGIN DECLARE i INT DEFAULT 0; DECLARE returnstr VARCHAR(255) DEFAULT ''; DECLARE c VARCHAR(1) DEFAULT '+'; char_add_loop: REPEAT SET i = i+1; SET returnstr = CONCAT( returnstr, c ); UNTIL i >= n END REPEAT; RETURN returnstr; END$$ DELIMITER ; SELECT repeat_plus(5); Code Explanation One parameter is passed to the function repeat_plus(): an integer n to indicate how many pluses (+) to add. The return type is a string (data type VARCHAR(255)). Some preliminary parameter validation is performed. The output string contains + repeated n times. WHILE The instructions between DO and END WHILE are executed as long as the condition is satisfied. Since the condition is evaluated at the beginning of the loop, it can happen that the loop is not executed even once (namely, when the condition is not satisfied on its initial evaluation). If you wish to use LEAVE or ITERATE, you must provide the loop with a label. Syntax [loopname:] WHILE condition DO commands; END WHILE [loopname]; LOOP The instructions LOOP and END LOOP are executed until the loop is exited with LEAVE loopname. It is not syntactically required that the loop be given a name, but in practice, it is usually done (unless you wish to create an infinite loop). Syntax loopname: LOOP commands; END LOOP loopname; Here is an example of a LOOP, to cut a given number. Code Sample: Programming/Demos/Function-Loop.sql DELIMITER ; DROP FUNCTION IF EXISTS cut_number; DELIMITER $$ CREATE FUNCTION cut_number(startnum INT, cutby INT, times INT) RETURNS INT BEGIN DECLARE i INT DEFAULT 0; DECLARE s TEXT DEFAULT ''; DECLARE cut INT DEFAULT 0; SET cut = startnum; cutloop: LOOP IF i >= times THEN LEAVE cutloop; END IF; SET cut = cut - cutby; SET i = i + 1; END LOOP cutloop; RETURN cut; END$$ DELIMITER ; SELECT cut_number( 80, 16, 4 ); SELECT cut_number( 100, 20, 5 ); SELECT cut_number( 120, 4, 6 ); Code Explanation Three parameters are passed to the function cut_number(): a starting number, what number to cut and how many times. The return type is a reduced number. LEAVE and ITERATE As seen above, LEAVE loopname exits the loop block. LEAVE can also be used to exit a BEGIN-END block out-of-turn. ITERATE loopname has the effect that the remaining part of the loop body is ignored and the loop is executed again. ITERATE only works with loops and cannot be used with a regular BEGIN-END block. Here is an example of a WHILE loop to cut a given number, but ignore the cut every third run. Code Sample: Programming/Demos/Function-Iterate.sql DELIMITER ; DROP FUNCTION IF EXISTS cut_number_except3; DELIMITER $$ CREATE FUNCTION cut_number_except3(startnum INT, cutby INT, times INT) RETURNS INT NO SQL BEGIN DECLARE i INT DEFAULT 0; DECLARE s TEXT DEFAULT ''; DECLARE cut INT DEFAULT 0; SET cut = startnum; cutloop: WHILE ( i < times ) DO SET i = i + 1; IF i % 3 = 0 THEN ITERATE cutloop; END IF; SET cut = cut - cutby; END WHILE; RETURN cut; END$$ DELIMITER ; SELECT cut_number_except3( 60, 10, 6 ); SELECT cut_number_except3( 80, 16, 4 ); SELECT cut_number_except3( 100, 20, 5 ); SELECT cut_number_except3( 120, 4, 6 ); Code Explanation As before, three parameters are passed to the function cut_number(): a starting number, what number to cut and how many times. The return type is a reduced number. ITERATE is used to continue the loop every third run. Exercise: Create a stored procedure Duration: 20 to 30 minutes. In this exercise, you will create a stored procedure to find customer rental summary. 1. Create a stored procedure with four parameters - start date, end date, first name, last name. 2. The procedure will return the first name, last name of customer, total rental count and total payments for the given period. 3. The first name and last name parameters will be used for partial matching - Any name containing those parts should show. 4. Optional: You can supply a third parameter to suggest how the match srings are to be used - starts with, ends with, contains. Where is the solution? Security Only users with Create Routine and Alter Routine privileges may create, change, or delete SPs. The privilege Execute determines which MySQL users are allowed to execute SPs. As SPs may contain several DML operations, some sensitive and critical, such as updating an employee's salary. So even if a user is permitted to execute SPs, some control of permitted database operations (that are defined in the SP) is critical. For example, can a table such as salary be updated via a particular SP invocation?. MySQL identifies two distinct situations: * SQL SECURITY INVOKER: Routines with this option have the same access privileges as the MySQL user executing the routine. So, an SP can delete records from table(s) if the user running that SP has the GRANT to do so. * SQL SECURITY DEFINER: SPs with this option have the same privileges as the MySQL user who defined the SP. This security mode holds by default; that is, if the option SQL SECURITY INVOKER is not taken, then DEFINER holds. Warning: Routines defined by privileged users such as admin or root have unrestricted privileges in all databases, and can cause a lot of hidden damage to data. Using MySQL Query Browser One can also use the MySQL Query Browser. Defining an SP: First select the database mylibrary via FILE | SELECT SCHEMA and then execute the command SCRIPT | CREATE STORED PROCEDURE. After you have specified the desired name and type (procedure or function), the Query Browser presents a template for you to complete with code. When you are finished, store the new SP with EXECUTE. If you have committed a syntax error, you must halt execution with STOP. Correct the error and try again. Testing the SP: To try out your new function, execute FILE | NEW QUERY TAB , give the command SELECT reduce(description, 20), title FROM film, execute it with EXECUTE. Editing an SP: While you have the dialog for defining the function reduce open, you can easily edit it. (Remember to EXECUTE.) Error Handling via Handlers It is likely that SP will encounter errors may occur during the execution of SQL commands. MySQL therefore provides a limited mechanism via handlers to react to such errors. A handler must be defined after the declaration of variables, cursors, and conditions but before the SQL commands of a BEGIN-END block, as shown: Syntax DECLARE type HANDLER FOR condition1, condition2, condition3, ... statement; * type: Presently, types CONTINUE and EXIT are allowed. The CONTINUE specifies that procedure execution continues with the next command after the occurrence of an error. EXIT means that the BEGIN-END block is exited and the program continued after the block. * condition(s): The conditions are what cause the handler to be invoked. There are several ways to specify the error conditions: o SQLSTATE 'errorcode': Specifies a particular error code. o SQLWARNING: Covers all 01nnn SQLSTATEs. o NOT FOUND: Covers all other errors, where SQLSTATEs that do not begin with 01 or 02. o mysql-error-number: MySQL error number instead of the SQLSTATE code. o condition-name: Refers to a condition that was named with DECLARE CONDITION . * statement: Statement to be executed when conditions are matched. The statement must be specified for a EXIT-type HANDLER as well, however simple it may be. A list of all MySQL error codes and the associated SQLSTATE values can be found in the online MySQL documentation at http://dev.mysql.com/doc/mysql/en/error-handling.html. Named Conditions Naming Conditions make cryptic error codes somewhat more readble. A condition must be declared before a handler as the name of the condition can then be used in the handler: Syntax DECLARE name CONDITION FOR condition; The following example shows the declaration of a variable, a condition, and a handler for the error duplicate key. DECLARE error_msg VARCHAR(200); DECLAREerror_condition CONDITION FOR SQLSTATE '23000'; DECLARE CONTINUE HANDLER FOR error_condtion SET error_msg = 'An Errror has occurred.'; Here is a detailed example to show error handling: Code Sample: Programming/Demos/Proc-Error-Handler.sql DELIMITER ; DROP TABLE IF EXISTS error_log; CREATE TABLE IF NOT EXISTS error_log (error_code VARCHAR(200), error_message VARCHAR(4000)); DROP PROCEDURE IF EXISTS `delete_category`; DELIMITER $$ CREATE PROCEDURE `delete_category`(p_category_id INT) BEGIN DECLARE `CategoryInUse` CONDITION FOR 1451; DECLARE EXIT HANDLER FOR `CategoryInUse` INSERT INTO error_log(error_code,error_message) VALUES( '1451', CONCAT( 'Time:', current_date, ':*** Error: Category has references, cannot delete Category ID = ', p_category_id ) ); START TRANSACTION; BEGIN DELETE FROM category WHERE category_id = p_category_id; COMMIT; END; ROLLBACK; END$$ DELIMITER ; CALL `delete_category`(10); SELECT * FROM category WHERE category_id = 10; SELECT * FROM error_log; Code Explanation Change delimiting to $$ to allow for semicolon use inside of procedure: delimiter $$ A category ID is are passed to the procedure. An attempt is made to delete category with the given ID. An error condition and an error handler have been defined in the procedure. The error handler records an error condition in an error_log table. Triggers Triggers are automatic execution of SQL commands or a stored procedure automatically after or before INSERT, UPDATE, or DELETE commands. For example, you can test every UPDATE operation to see whether the altered data conform to a particular set of rules. Other possible applications include changes to logging and updating variables or columns in other tables. Since trigger code is automatically executed at each change in the table, complex triggers can sharply reduce data throughput performance. This is particularly true when a command changes a large number of records (UPDATE table SET columnA = columnA + 1); the trigger code must be executed for each data record. Note: The trigger implementation in MySQL 5.0 is unfortunately not very complete. Triggers will probably not be of practical use until version 5.1. Creating a Trigger You create a new trigger with the command CREATE TRIGGER. Only MySQL users with the Super privilege can execute this command. Syntax CREATE TRIGGER name BEFORE|AFTER INSERT|UPDATE|DELETE ON tablename FOR EACH ROW sql-code You can define up to six triggers for each table, whose code is to be executed before or after each INSERT, UPDATE, or DELETE command. If the command encompasses more than one record, the trigger will be executed for each record. Currently (MySQL 5.0.3), each trigger must have a unique name within a table. In the future, trigger names will be valid for the entire database. There are similar syntax rules for trigger code as for SPs. In particular, the code must be introduced with BEGIN and terminated with END if it contains more than one command. All SP language elements are available for triggers. However, there are many restrictions as to which SQL commands may be used in trigger code (more on this below). Within the trigger code you can access the columns of the current record: * OLD.columnname returns the content of an existing record before it is changed or deleted (UPDATE, DELETE). * NEW.columnname returns the content of a new or altered record (INSERT, UPDATE). * You may change NEW.columnname in BEFORE INSERT triggers and BEFORE UPDATE triggers. Deleting a Trigger To delete a trigger, execute DROP TRIGGER. You need to specify the table and trigger names. DROP TRIGGER tablename.triggername Code Sample: Programming/Demos/Create-Trigger.sql \T trig_name.log DROP TABLE IF EXISTS trig_film; CREATE TABLE trig_film(film_id SMALLINT NOT NULL PRIMARY KEY, title VARCHAR(255), rating VARCHAR(25), category VARCHAR(25), price FLOAT); DROP TRIGGER IF EXISTS film_before_insert; DROP TRIGGER IF EXISTS film_before_update; DROP TRIGGER film_before_insert; DROP TRIGGER film_before_update; DELIMITER $$ CREATE TRIGGER film_before_insert BEFORE INSERT ON trig_film FOR EACH ROW BEGIN -- Force price to 1 for children movies IF NEW.rating = 'PG' AND NEW.category = 'Children' THEN SET NEW.price = ROUND(NEW.price / 2, 2); END IF; IF NEW.price > 4.0 THEN SET NEW.price = 4.0; END IF; -- Notch up ratings if category is foreign IF NEW.category = 'Foreign' THEN IF NEW.rating = 'PG' THEN SET NEW.rating = 'PG-13'; ELSEIF NEW.rating = 'PG-13' THEN SET NEW.rating = 'NC-17'; END IF; END IF; END$$ CREATE TRIGGER film_before_update BEFORE UPDATE ON trig_film FOR EACH ROW BEGIN -- Force price to 1 for children movies IF NEW.rating = 'PG' AND NEW.category = 'Children' THEN SET NEW.price = ROUND(NEW.price / 2, 2); END IF; IF NEW.price > 4.0 THEN SET NEW.price = 4.0; END IF; -- Notch up ratings if category is foreign IF NEW.category = 'Foreign' THEN IF NEW.rating = 'PG' THEN SET NEW.rating = 'PG-13'; ELSEIF NEW.rating = 'PG-13' THEN SET NEW.rating = 'NC-17'; END IF; END IF; END$$ DELIMITER ; INSERT INTO trig_film(film_id, title, rating, category, price) SELECT film_id, title, rating, category_name, rental_rate FROM film_detail; SELECT title, price FROM trig_film WHERE rating = 'PG' AND category = 'Children' LIMIT 20; SELECT title, rating FROM trig_film WHERE category = 'Foreign' LIMIT 20; UPDATE trig_film SET price = 3.99 WHERE rating = 'PG' AND category = 'Children' AND price >= 1.5; UPDATE trig_film SET price = 4.99 WHERE category = 'Horror' AND price >= 3.99; UPDATE trig_film SET rating = 'PG' WHERE category = 'Foreign' AND rating = 'PG-13'; UPDATE trig_film SET rating = 'PG-13' WHERE category = 'Foreign' AND rating = 'NC-17'; SELECT title, price FROM trig_film WHERE rating = 'PG' AND category = 'Children' LIMIT 20; SELECT category, title, rating FROM trig_film WHERE category = 'Foreign' LIMIT 20; SELECT category, title, price FROM trig_film WHERE category = 'Horror' LIMIT 20; \t Code Explanation The update and insert triggers ensure that: * Price is made half for PG/Children films * Price cannot be set over 4 bucks * Ratings are adjusted for Foreign films, 'PG' becomes 'PG-13', 'PG-13' becomes 'NC-17' The script also show dropping of triggers. Implementation Details and Administrative Assistance Currently (MySQL 5.0.3), MySQL trigger code is stored in the text file tablenname.TRG in the database directory. Furthermore, there is currently is no real user interface/administrative help for Triggers: MySQL Query Browser, MySQL Administrator, phpMyAdmin, etc., know nothing about triggers. To CREATE TRIGGERs, one must use the command interpreter mysql. To backup defined triggers, back up the .TRG files in the database directory. The option IF EXISTS is supported in later releases of MySQL 5.0. There may not be any administrative commands like ALTER TRIGGER, SHOW CREATE TRIGGER, or SHOW TRIGGER STATUS. SHOW TRIGGERS is available in later releases of MySQL 5.0 onwards. Code Sample: Programming/Demos/Show-Triggers.sql SHOW TRIGGERS; * One cannot access tables in trigger code, not even the table for which the trigger was defined. As with SP functions, the commands SELECT, UPDATE, INSERT, etc., are unavailable. * There are no commands or language elements to cancel the DELETE, UPDATE, or INSERT command from within a trigger or to raise an error. * Trigger code cannot call an SP with CALL. * No transaction commands can be called in trigger code. Code Sample: Programming/Demos/Create-Trigger-Status.sql DROP TABLE IF EXISTS trig_test; CREATE TABLE trig_test (idcol SERIAL, rate FLOAT, status ENUM('low','high','orig') DEFAULT 'orig' ); DELIMITER $$ DROP TRIGGER IF EXISTS trig_test.trig_test_before_insert; DROP TRIGGER IF EXISTS trig_test.trig_test_before_update; CREATE TRIGGER trig_test_before_insert BEFORE INSERT ON trig_test FOR EACH ROW BEGIN IF NEW.rate < 0.0 THEN SET NEW.rate = 0.0; SET NEW.status = 'low'; ELSEIF NEW.rate > 5.0 THEN SET NEW.rate = 5.0; SET NEW.status = 'high'; END IF; END$$ CREATE TRIGGER trig_test_before_update BEFORE UPDATE ON trig_test FOR EACH ROW BEGIN IF NEW.rate < 0.0 THEN SET NEW.rate = 0.0; SET NEW.status = 'low'; ELSEIF NEW.rate > 5.0 THEN SET NEW.rate = 5.0; SET NEW.status = 'high'; END IF; END$$ DELIMITER ; INSERT INTO trig_test (rate) VALUES (-1), (0.3), (5.5), (3.5), (-0.2), (4.2); SELECT * FROM trig_test; UPDATE trig_test SET rate = 1.7 WHERE idcol = 2; SELECT * FROM trig_test; Code Explanation 1. Create a table with an ID, rate and an status enumeration. 2. The status enumeration can take values as 'low', 'high' or 'orig'. 3. Create two triggers, one for update and another for insert. 4. Validate rate in triggers, ensure the lowest value allowed is (and set to) 0 and the highest value allowed to 5. 5. While in the trigger, record any trigger-based adjustment made to rate in the status field Exercise: Create a trigger Duration: 15 to 20 minutes. In this exercise, you will create a trigger to validate a category. 1. Create a trigger on category for insert and update. 2. Check category name before writing to the table to ensure that 'z', 'q' and 'x' characters in the name are replaced with a '+' for INSERT and a '.' for UPDATE. 3. All other characters in the name are left as is. 4. Hint: You may have to create and copy to another table, say category_trig, as the original category already has some triggers on it. It may be best to replicate both structure and data from the original category table. Where is the solution? Programming with MySQL Conclusion This lesson introduced you to programming elements of MySQL. To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.