Tutorial: how to create a trigger program in MYSQL _ MySQL

Tutorial: the method for creating a triggering program for MYSQL has just understood how to create a triggering program for MYSQL.

Syntax: create trigger trigger_name trigger_time trigger_event
ON tbl_name for each row trigger_stmt

The text description is not very clear when I first read it, but the last example is almost the same!

A trigger is a table-Related named database object. this object is activated when a specific event occurs on the table.

The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. You cannot associate the trigger program with the TEMPORARY table or view.

Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER to indicate that the trigger program is triggered BEFORE or AFTER its statement is activated.

Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:

? INSERT: The Trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.

? UPDATE: The Trigger program is activated when a row is changed, for example, through the UPDATE statement.

? DELETE: The Trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

Note that trigger_event is not very similar to the SQL statement used to activate the trigger program in the form of table operations, which is very important. For example, the BEFORE trigger program for INSERT can be activated by both the INSERT statement and the load data statement.

One of the examples that may cause confusion is insert .. on duplicate update... syntax: the before insert trigger will be activated for each row, followed by the after insert trigger Program, or the before update and after update trigger programs, depending on whether there is a duplicate key on the row.

For a given table with the same triggering program action time and event, there cannot be two triggering programs. For example, a table cannot have two before update triggers. However, there can be one before update trigger program, one before insert trigger program, one before update trigger program, and one after update trigger program.

Trigger_stmt is the statement executed when the trigger program is activated. If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, the same statements allowed in the stored subroutine can be used.

The OLD and NEW aliases can be used to reference columns in tables related to the trigger program. OLD. col_name references one column in an existing row before updating or deleting it. NEW. col_name references the column 1 of the NEW row to be inserted or the column 1 of the existing row after it is updated.

When the trigger program is activated, the SELECT permission is required for all the OLD and NEW columns referenced by the trigger program. The UPDATE permission is required for all NEW columns used as the SET value assignment target.

Note: Currently, the trigger program will not be activated by Cascade foreign key actions. This restriction will be relaxed as soon as possible.

The create trigger statement requires the SUPER permission.

Example:

Create trigger update_blogs after insert on blogs FOR EACH ROW
UPDATE blogcategory SET amount = amount + 1 WHERE cid = NEW. categoryid;

This statement creates a trigger program named update_blogs, this trigger executes "UPDATE blogcategory SET amount = amount + 1 WHERE cid = NEW. categoryid ;"