MySQL Trigger (Trigger) Concise Summary and usage examples

Reprint, original link

One, what triggers

1, personal understanding
Flip-flops, literally understood, trigger a device, called triggers (haha, personal understanding), for example, like dark, you turn on the lights, you see things. You put the firecracker, lit, and then blew up.
2, official definition
A trigger (trigger) is a special stored procedure whose execution is not invoked by the program or manually, but is triggered by an event, such as when an operation on a table (Insert,delete, update) activates it for execution. Triggers are often used to enforce data integrity constraints, business rules, and so on. Triggers can be found in the dba_triggers, user_triggers data dictionary.
A very good feature of triggers is that triggers can suppress or rollback changes that violate referential integrity, thereby canceling the attempted data modification.
What do you mean, give an example to explain, arcade game everyone has played, rushed through a level, rushed to the next level, there is a close did not rush to start from the first off. The trigger root is similar.
The official explanation is as follows
The trigger is considered to be part of a single transaction, so the transaction can be restored by the original trigger, and the entire transaction will be automatically restored if a serious error is detected during the transaction, such as the user's interrupt connection.
His role is clear. Data integrity, there is an example below to illustrate his benefits, and if the code is less complex to write

Second, trigger syntax

CREATE TRIGGER trigger_name trigger_time trigger_event on tbl_name for each    ROW trigger_stmt

　　

A trigger is a named database object related to a table that is activated when a specific event occurs on the table.
The trigger is related to a table named Tbl_name. Tbl_name must reference a persistent table. You cannot associate a trigger with a temporary table or view.
Trigger_time is the action time of the triggering program. It can be before or after to indicate that the trigger was triggered before or after the statement that activated it.
Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:
· Insert: Activates the trigger when inserting a new row into the table, for example, through the INSERT, LOAD data, and replace statements.
· Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.
· Delete: Activates the trigger when a row is deleted from the table, for example, through the Delete and replace statements.
It is important to note that trigger_event is not very similar to the SQL statement that activates the trigger as a table operation. For example, the before trigger on insert can be activated not only by the INSERT statement, but also by the load data statement.
One example of possible confusion is insert into. On DUPLICATE UPDATE ... Syntax: The before INSERT trigger is activated for each row, followed by the after insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.
There cannot be two triggers for a given table with the same trigger action time and event. For example, you cannot have two before update triggers for a table. However, there can be 1 before update triggers and one before insert trigger, or 1 before update triggers and one after UPDATE trigger.
TRIGGER_STMT is the statement that executes when the triggering program is activated. If you plan to execute multiple statements, you can use the BEGIN ... End Compound statement structure. This allows you to use the same statements that are allowed in the stored subroutine

Three, create the hair generator

1, User table users

CREATE TABLE ' user ' (  ' id ' int (one) ' NOT NULL auto_increment COMMENT ' userid ',  ' name ' varchar (NOT NULL default ') ' COMMENT ' name ',  ' sex ' int (1) Not NULL default ' 0 ' COMMENT ' 0 for male, 1 for female ',  PRIMARY KEY  (' id ')  ) engine=myisam
   default Charset=utf8;  INSERT into ' user ' (' id ', ' name ', ' sex ') VALUES  (1, ' Zhang Ying ', 0),  

　　

2, Comment form comment

CREATE TABLE ' comment ' (  ' c_id ' int (one) not null auto_increment comment ' Comment id ',  ' u_id ' int (one) not null comment ' with User ID ',  ' name ' varchar (NOT null default ' COMMENT ' username ') ',  ' content ' varchar (+) ' NOT null ' ' COMMENT ' Comment content ',  PRIMARY KEY  (' c_id ')  ) Engine=myisam  DEFAULT Charset=utf8;  INSERT into ' comment ' (' c_id ', ' u_id ', ' name ', ' content ') VALUES  (1, 1, ' Zhang Ying ', ' trigger test '),  (2, 1, ' Zhang Ying ', ' solve field redundancy '), 
    (3, 2, ' tank ', ' make the Code simpler ');  

　　

Here is a redundant field name, we can use federated search to find the name of the user table in the read comment, why should have redundant fields, because the simple SQL statement execution is more efficient, but not redundant field more the better, redundant fields are more, also increase the database burden.
What I want to do is, when I update the name of the user table, the trigger updates the comment table at the same time, do not write PHP code to update, when the user is deleted, the comment table, the data about the user will be deleted

3, update name trigger

delimiter | |      The MySQL default end sign is a semicolon, and when you write a trigger or stored procedure with a semicolon, it aborts instead of executing the  drop trigger if exists updatename| |    Delete the trigger with the same name,  create trigger updatename after update on the user for each row   //Establish a trigger,  begin  //old, New is the record line representing the current operation, and you can think of it as a table name;  If New.name!=old.name then   //When the user name in the table changes, perform  update comment set comment.name=new.name where comment.u_id= Old.id;  End If;  end| |  delimiter;

　　

4, trigger Delete comment data

delimiter | |  Drop trigger if exists deletecomment| |  Create trigger deletecomment before delete on user for each row  begin  Delete from comment where comment.u_id=old . ID;  end| |  delimiter;  

One thing is very depressing, is to write a good trigger code, can not be modified, you want to delete the reconstruction, depressed, on the other is phpMyAdmin, some can create a trigger, some can not, some could create, but created to see. In the study.

5, test whether the trigger is available

A, test Updata trigger

Update user set Name= ' Eagle '  where id = 1;

　　

After the update, go to the comment table to see, inside the Name field inside the paragraph has not changed

b, test the Delete trigger

Delete from user  where id = 1;

　　

After the update, go to the comment table to see, inside the Name field inside the paragraph has not changed

Four, the advantages of the trigger

1, the "automatic" of the trigger
For programmers, the trigger is not visible, but he did do things, if you do not use a trigger, you update the user table name field, you also write code to update the other tables in the redundant fields, I give an example, just a table, if a few tables have redundant fields, Does your code have to be a lot of writing, does it look uncomfortable?
2, data integrity of triggers
Triggers have rollback, for example, I find that I like to lift up, that is, you want to update five tables of data, do not appear to update two tables, and the other three tables are not updated.
But if it is written in PHP code, it is possible that this situation, such as you update the data of two tables, this time, the database is dead. You are depressed, some updates, some are not updated. This page shows inconsistencies, and has become a bug.

MySQL Trigger (Trigger) Concise Summary and usage examples