MySQL uses triggers (Trigger) to make the code easier

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

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

    CREATE TABLE`User' (' ID 'int( One) not NULLAuto_increment COMMENT'User ID', ' name 'varchar( -) not NULL default "'COMMENT'name', ' sex 'int(1) not NULL default '0'COMMENT'0 for men, 1 for women',       PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8; INSERT  into`User' (' id ', ' name ', ' sex ')VALUES       (1,'Zhang Ying',0),       (2,'Tank',0);

ID	name	Sex
1	Zhang Ying	0
2	Tank	0

2, Comment form comment

CREATE TABLE' comment ' (' c_id ' )int( One) not NULLAuto_increment COMMENT'Comment ID', ' u_id 'int( One) not NULLCOMMENT'User ID', ' name 'varchar( -) not NULL default "'COMMENT'User name', ' content 'varchar( +) not NULL default "'COMMENT'Comment Content',   PRIMARY KEY(' c_id ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8; INSERT  into' Comment ' (' c_id ', ' u_id ', ' name ', ' content ')VALUES   (1,1,'Zhang Ying','Trigger Test'),   (2,1,'Zhang Ying','Resolve Field Redundancy'),   (3,2,'Tank','make your code easier');

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

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 a stored procedure, a semicolon appears, aborting the execution     Drop Trigger if existsUpdateName||    // Delete the trigger with the same name.       Create TriggerUpdateName afterUpdate  on User  forEach row//Create a trigger,     begin       //Old,new is the record line representing the current operation, and you can do it as a table name;      ifNew.name!=Old.name Then   //When the user name changes in the table, the execution     UpdateCommentSetComment.name=New.namewherecomment.u_id=old.id; End if; End||delimiter;

3, trigger delete comment data

Delimiter||       Drop Trigger if existsDeleteComment||       Create TriggerDeleteComment beforeDelete  on User  forEach rowbegin       Delete  fromCommentwherecomment.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. 4, test whether the trigger is available

A, test Updata trigger
Update User Set name=' Eagle '  where=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 = 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.

MySQL uses triggers (Trigger) to make the code easier