Trigger

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table and that activates when a particular event occurs for the table. For example, the following statements create a table and an INSERT trigger. The trigger sums the values inserted into one of the table's columns:

- create trigger syntax

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

The trigger becomes associated with the table named tbl_name. tbl_name must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after the statement that activated it.

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following:

INSERT: The trigger is activated whenever a new row is inserted into the table, for example through INSERT, LOAD DATA, and REPLACE statements.
UPDATE: The trigger is activated whenever a row is modified, for example through UPDATE statements.
UPDATE: The trigger is activated whenever a row is modified, for example through UPDATE statements.
DELETE: The trigger is activated whenever a row is deleted from the table, for example through DELETE and REPLACE statements.

example :

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

If you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

Then the data in the four tables will be as follows:

mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)

stored procedure and function

Stored procedures and functions are routines that are created with CREATE PROCEDURE and CREATE FUNCTION statements. A routine is either a procedure or a function. A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value. Stored routines may call other stored routines.

- create procedure CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

proc_parameter: [ IN OUT INOUT ] param_name type

- CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body

func_parameter:
param_name type

type: Any valid MySQL data type
characteristic:
LANGUAGE SQL
[NOT] DETERMINISTIC
{ CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA } SQL SECURITY { DEFINER INVOKER } COMMENT 'string'
routine_body: Valid SQL procedure statement or statements