The role of the MySQL Trigger (Trigger)

One, what triggers

1, personal understanding

The function of the trigger is to achieve synchronization between the two libraries, that is, the operation of library A and so on in the operation of library B, you can create a change on the table a trigger, to implement the other table or other operations.

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 and business rules. 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

trigger_name Trigger_time trigger_event

Tbl_name 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 thattrigger_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

1 CREATE TABLE ' user ' (2' id ' int (one) notNULLAuto_increment COMMENT ' User ID ',3' Name ' varchar (not)NULL default' COMMENT ' name ',4' Sex ' int (1) notNULL default' 0 ' COMMENT ' 0 for men, 1 for women ',5PRIMARYKEY(' id ')6) Engine=myisamDEFAULTcharset=UTF8; 7       8INSERT into ' user ' (' id ', ' name ',' sex ') VALUES9(1, ' Zhang Ying ', 0),Ten(2, ' tank ', 0);

ID	name	Sex
1	Zhang Ying	0
2	Tank	0

2, Comment form comment

1 CREATE TABLE ' comment ' (2' c_id ' int (one) notNULLAuto_increment COMMENT ' Comment ID ',3' u_id ' int (one) notNULLCOMMENT ' User ID ',4' Name ' varchar (not)NULL default' COMMENT ' user name ',5' Content ' varchar (+) notNULL default' COMMENT ' review content ',6PRIMARYKEY(' c_id ')7) Engine=myisamDEFAULTcharset=UTF8; 8       9INSERT into ' comment ' (' c_id ', ' u_id ', ' name ',' content ') VALUESTen(1, 1, ' Zhang Ying ', ' trigger test '), One(2, 1, ' Zhang Ying ', ' solve field redundancy '), A(3, 2, ' tank ', ' make the Code simpler ');

c_id	u_id	name	content
1	1	Zhang Ying	Trigger Test
2	1	Zhang Ying	Resolve Field Redundancy
3	2	Tank	Make your code easier

Redundant fields are recurring, repeating fields in a database where fields in a table appear in two tables, called redundant fields.

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. Format of the trigger

For example, adding a data to table a while adding (deleting/modifying) it in table B

1Delimiter | |MySQL ends with a semicolon, plus delimiter ends with this. 2Drop TriggerifExists updatename| |//Delete a trigger with the same name3 CREATE trigger A_insert (trigger name)4Before before/after insert on a (A is the table name,that's the watch you're working on.)5  for  eachrow (fixed format)6 begin7INSERT into B values (column 1, column 2, column 3), Range insert/update/delete inside;8 End||9Delimiter

4, update name trigger

1Delimiter | |//The MySQL default end sign is a semicolon, and when you write a trigger or a stored procedure, a semicolon appears, aborting the execution2Drop TriggerifExists updatename| |//Delete the trigger with the same name.3Create trigger UpdateName after update on user for  eachRow//Create a trigger,4 begin5 //Old,new is the record line representing the current operation, and you can do it as a table name; 6 if New. Name!=old.name Then//When the user name changes in the table, the execution7Update comment Set Comment.name=New. name where Comment.u_id=old.ID; 8 End if; 9 End||

5, trigger delete comment data

1 delimiter | |   2 if exists deletecomment| |   3  for  each Row    4 begin    5 Delete from comment where comment.u_id=old. ID;    6 End| |   7 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. 6, test whether the trigger is availableA, test Updata trigger update user set Name= ' eagle ' where id = 1; After the update go to the comment table inside see, Inside the Name field inside the paragraph has not changed B, test delete trigger delete from user wher e id = 1; After the update, go to the comment table to see, inside the Name field inside the paragraph has not changed Four. Advantages of the trigger 1, the "automatic" of the triggerFor 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 triggersTriggers 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.

Ps:
1) between Begin...end can write any statement you want to implement, the above is created by a add trigger, you can also create a delete and modify the trigger, simply change the keyword insert to update or delete. (Explanation: When you need to make an additional operation on a table, you need to do another action, you can implement it with a trigger.)
2) How to know what data you are working with in a trigger
  Add: Can be queried in the temp table new
  Delete: Can be queried in temporary table old
  Modification: The modified data is queried in new, and the modified data is queried in old.
  For example: SELECT *  from new (query the data you inserted  Note: temporary tables old and new are only available if the trigger is triggered, other things are not!)
Part of the article from the Internet, if there is infringement, please inform.

The role of the MySQL Trigger (Trigger)