How to use MySql triggers _ MySQL

Original works of Lin Bingwen Evankaka. For more information, see http: blogcsdnnetevankaka TRIGGER. These events include INSERT statements, UPDATE statements, and DELETE statements. When the database system executes these events, the trigger is activated to execute the corresponding operations. MySQL supports triggers from version 5.0.2. The content described in this article includes:

Trigger meaning and function
How to create a trigger
How to View triggers
How to delete a trigger

A trigger is triggered by INSERT, UPDATE, DELETE, and other events. When the trigger conditions are met, the database system executes the program statements defined in the trigger. This ensures consistency between some operations. For example, when a student information is added to the student table, the total number of students must be changed at the same time. You can create a trigger here to calculate the total number of students every time you add a student record. This ensures that the total number of students is consistent with the number of records after each additional student records. The trigger may trigger only one or more execution statements. This section describes how to create a trigger.

I. Syntax

Create a trigger with only one execution statement

In MySQL, the basic form of a trigger with only one execution statement is as follows:

Create trigger name BEFORE | after trigger event ON table name for each row execution statement

Create a trigger with multiple execution statements

In MySQL, the trigger may trigger multiple execution statements. The basic form of a trigger with multiple execution statements is as follows:

Create trigger name BEFORE | after trigger event ON table name for each rowbegin execution statement list END

The following describes the syntax.

1. naming rules

CREATE TRIGGER
 <触发器名称>
  
<-- {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON
  <表名称>
   
FOR EACH ROWBEGIN
   <触发器sql语句>
    
END
   
  
 

The trigger must have a name and a maximum of 64 characters. it may be followed by a separator. it is similar to the naming method of other objects in MySQL.
2. Trigger Time: BEFORE | AFTER
Trigger execution time settings: INSERT | UPDATE | DELETE
3. before and after the event is triggered
The trigger events can also be set: they can be triggered during the execution of insert, update, or delete.
4. Tables
A trigger belongs to a table: When an insert,
The trigger is activated when the update or delete operation is performed.
We cannot schedule two triggers for the same event of the same table.
5. (step) trigger interval
Trigger execution interval: for each row clause notifies trigger
Execute an action on every row instead of the entire table.
6. statements
The trigger contains the SQL statement to be triggered: the statement can be any legal statement,
Including compound statements, but the statements here are subject to the same restrictions as those of functions.
Privileges permission
You must have considerable permissions to CREATE a TRIGGER ). If you are a Root user, it is enough. This is different from the SQL standard.

2. Identify the old and newly created columnsIn the SQL statement of the trigger, you can associate any column in the table. However, you cannot only use the column name to identify it, which will confuse the system, because there may be a new column name (this may be exactly what you want to modify, your action may be to modify the column name), and the old name of the column exists. Therefore, you must use this syntax to Mark: "NEW. column_name "or" OLD. column_name ". in this way, it is technically processed (NEW | OLD. column_name) the new and old column names belong to the created transition variable ("transition variables ").

For INSERT statements, only NEW statements are valid; for DELETE statements, only OLD statements are valid; and UPDATE statements can be used together with NEW and OLD statements.

III. Examples

Create two tables, one order table, and one order time table

CREATE TABLE T_ORDER(ORDER_NUM INT PRIMARY KEY,ORDER_NAME CHAR(10) NOT NULL);CREATE TABLE T_ORDER_TIME(ORDER_NUM INT PRIMARY KEY,ORDER_TIME TIMESTAMP NOT NULL,)

1. create an insert trigger

CREATE TRIGGER TRI_INSERTAFTER INSERT ON T_ORDER FOR EACH ROWBEGININSERT INTO learning.t_order_time(ORDER_NUM,ORDER_TIME) VALUES(NEW.ORDER_NUM,NOW());END

Insert a data entry into the order table.

Insert into T_ORDER (ORDER_NUM, ORDER_NAME) VALUES (1, 'PC'); SELECT * FROM t_order; SELECT * FROM t_order_time;

This is the data inserted in the order table:

This is the data automatically inserted in the order schedule:

2. Insert data during update

// Update create trigger TRI_UPDATABEFORE update on T_ORDER for each rowbeginupdate learning. t_order_time SET ORDER_TIME = NOW () where old. ORDER_NUM = ORDER_NUM; ENDUPDATE T_ORDER SET ORDER_NAME = 'biscuit Pie 'WHERE ORDER_NUM = 1; SELECT * FROM t_order; SELECT * FROM t_order_time;

We can see that the time has been updated.

3. create a delete trigger

// Create trigger TRI_DELETEAFTER delete on T_ORDER for each rowbegindelete from learning. t_order_time where old. ORDER_NUM = ORDER_NUM; enddelete from learning. t_order WHERE ORDER_NUM = 1; SELECT * FROM t_order; SELECT * FROM t_order_time;

We can see that there is no data in the database.

4. view the trigger

A view trigger is used to view the definition, status, and syntax of an existing trigger in the database. You can view the trigger by using the show triggers statement and the triggers table in the information_schema database. This section describes how to view a trigger.

In MySQL, you can run the show triggers statement to view the basic information of the trigger. The basic form is as follows:

Show triggers;

In MySQL, all triggers are defined in the triggers table in the information_schema database. Query the triggers table to view the details of all triggers in the database. The query statement is as follows:

SELECT * FROM information_schema.triggers;

5. delete a trigger

Drop trigger name

6. triggers and stored procedures
The trigger program cannot CALL the storage program that returns data to the client, nor use dynamic SQL statements that use the CALL statement.
(Allows the storage program to return data to trigger programs through parameters ).
The stored procedure can accept parameters and give the results to the application.