MySQL Trigger usage explained

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

A trigger (TRIGGER) is an action triggered by an event. These events include INSERT statements, UPDATE statements, and DELETE statements. When the database system executes these events, the trigger is activated to perform the appropriate action. MySQL supports triggers starting from version 5.0.2. What you'll be explaining in this article will include:
Implications and effects of triggers
How to create a trigger
How to view triggers
How to delete a trigger

Triggers are events such as INSERT, UPDATE, and delete that trigger a specific action. When a trigger condition is met, the database system executes the program statement defined in the trigger. Doing so guarantees consistency between certain operations. For example, when a student's table is added to a student's information, the total number of students must be changed at the same time. You can create a trigger here, and each time you add a student's record, you perform a calculation of the total number of students. This ensures that every time a student's record is added, the total number of students is consistent with the number of records. The execution statement triggered by the trigger may have only one or more. This section explains how to create triggers in detail.

First, the grammar

Create a trigger that has only one execution statement

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

CREATE TRIGGER trigger name before | After trigger event on table name for each ROW execution statement

to create a trigger with more than one execution statement

In MySQL, there may be multiple execution statements triggered by triggers. The basic form of creating a trigger with multiple execution statements is as follows:

CREATE TRIGGER trigger name before | After trigger event on table name for each Rowbegin execution statement list end

Here's a concrete word for grammar.

1. Naming rules

CREATE TRIGGER < trigger name > <--{before | After} {INSERT | UPDATE | DELETE}on < table name >for each rowbegin< trigger SQL statement >end

The trigger must have a name, a maximum of 64 characters, and may be appended with a delimiter. It is basically like naming other objects in MySQL.
2, Trigger time: Before | after
Trigger has a time setting for execution: INSERT | UPDATE | DELETE
3, before and after the triggering event
It is also possible to set triggered events: they can be triggered during an insert, update, or delete operation.
4., table
A trigger is part of a table: When an insert is performed on this table,
The activation of the trigger is caused by an update or delete operation.
We cannot schedule two triggers for the same event in the same table.
5., (step) trigger interval
Execution interval of the trigger: the For each row clause notifies the trigger
Performs a single action on every other line, rather than once for the entire table.
6. Statements
The trigger contains the SQL statement that you want to trigger: The statement here can be any valid statement,
Include compound statements, but the statements here are constrained by the same limitations as functions.
Privileges Permissions
You must have considerable permissions to create the trigger (creation TRIGGER). If you're already a root user, that's enough. This is different from the SQL standard.


ii. identification of old and newly created columnsIn a trigger's SQL statement, you can associate any column in the table. But you can't just use the name of the column to identify it, which can confuse the system, because there may be a new name for the column (which is probably what you want to modify, your action might be to modify the column name), and the old name of the column exists. So you have to use this syntax to identify: "NEW." column_name "or" old. Column_name ". This is technically handled (NEW | Old. COLUMN_NAME) The new and old column names belong to the created transition variable ("transition variables").

Only new is valid for INSERT statements, and only old is valid for DELETE statements, while UPDATE statements can be used in conjunction with new and old.

Iii. Examples of Use

Create two tables, one order form, one order schedule

CREATE TABLE t_order (order_num INT PRIMARY key,order_name CHAR (Ten) not NULL); CREATE TABLE t_order_time (order_num INT PRIMARY key,order_time TIMESTAMP not NULL,)

1. Create a trigger when inserting

CREATE TRIGGER tri_insertafter INSERT on T_order-Rowbegininsert into Learning.t_order_time (order_num,order_time ) VALUES (NEW. Order_num,now ()); END

Then insert a piece of data into the order table

INSERT into T_order (order_num,order_name) VALUES (1, ' computer '); SELECT * from T_order; SELECT * from T_order_time;

This is the data inserted in the order table:

This is the data that is automatically inserted in the order schedule:

2. Insert data when updating

Update creation trigger 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= ' cookie pie ' WHERE order_num=1; SELECT * from T_order; SELECT * from T_order_time;

As you can see, the time has been updated

3. Create a DELETE trigger

Creating a Delete trigger create TRIGGER tri_deleteafter Delete on T_order-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;

You can see that there are no data in the database.

4. View triggers

Viewing a trigger is a view of information such as the definition, status, and syntax of a trigger that already exists in the database. The methods for viewing the trigger include the show triggers statement and querying the triggers table under the INFORMATION_SCHEMA database. This section explains how to view triggers in detail.

MySQL , you can perform SHOW TRIGGERS statement to view the basic information for the trigger. Its basic form is as follows:

SHOW TRIGGERS;

MySQL , all triggers are defined in the Information_schema under the database triggers table. Query the triggers table to see detailed information about all the triggers in the database. The query's statements are as follows:

SELECT * from INFORMATION_SCHEMA. triggers;

5. Delete Trigger

DROP TRIGGER Trigger Name

6. Triggers and stored procedures
The trigger cannot invoke the stored program that returns the data to the client, nor can it use dynamic SQL with the call statement
(Allows the stored program to return data to the trigger via parameters).
The stored procedure can accept parameters and give the result scope to the application

Copyright NOTICE: This article for Bo Master Lin Bingwen Evankaka original article, without Bo Master permission not reproduced.

MySQL Trigger usage explained