Mysql trigger examples

I’ve found a few examples of triggers. Check those source :

• the introductory [Frank Mash]
• the advance example [Frank Mash]
• More advance examples [Jan Kneschke]

Mysql store procedure example

Frank Mash has some examples of store procedure. Check it out

mysql> DROP PROCEDURE IF EXISTS build_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8976
Current database: odp

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE build_table()
-> BEGIN
-> DECLARE i INTEGER;
-> DECLARE v INTEGER;
-> SET i = 1;
-> SET v = 100;
-> WHILE i <= 125 DO
-> INSERT into mytable VALUES (i, v);
-> SET i = i + 1;
-> SET v = v + 2;
-> END WHILE;
-> END/
DELIMITER ';'/Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ';'/
mysql> DROP TABLE IF EXISTS mytable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mytable (id INTEGER, value INTEGER);
Query OK, 0 rows affected (0.04 sec)

mysql> CALL build_table();
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * from mytable LIMIT 0,1;
+------+-------+
| id | value |
+------+-------+
| 1 | 100 |
+------+-------+
1 row in set (0.00 sec)



[Frank Mash blog]