A brief talk on Starling MySQL Trigger

What is a MySQL trigger, let's look at the meaning of the trigger first. The literal meaning of a trigger is a reaction triggered by a touch.

MySQL must know that the trigger is interpreted as a MySQL statement (or a set of statements between the BEGIN and END statements) that is automatically executed by MySQL in response to any of the following statements. Where any of the following statements refers to: Delete,insert,update.

My understanding of the triggers in MySQL is a behavior that occurs when the data content of a MySQL database is changed (added, deleted, added).

Special NOTE: You cannot mobilize triggers when you are querying data (my understanding is that the contents of the database cannot be changed at this time the call trigger is meaningless because the trigger is a backup of the database , guaranteeing security, etc.). There are additions, deletions, additions to the behavior (or MySQL statements) that are executed after the trigger is mobilized.

The role and significance of triggers: Data backup, data synchronization, security assurance. Please see the following cases for details. SHOW TRIGGERS; View all the triggers in the table.

1. Case one, use triggers for data backup.

Scenario: In the student table, before deleting the student information with ID 3, insert the information you want to delete into the Studentbackup table for backup. Triggering behavior occurs when a delete is executed. The specific code is as follows (with parsing).

Student TableCREATE TABLE' student ' (' ID ')int( One) not NULL, ' name 'varchar(255)DEFAULT NULL, ' Zongfen 'int(255)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=GBK student Backup FormCREATE TABLE' Studentbackup ' (' ID ')int( One) not NULL, ' name 'varchar(255)DEFAULT NULL, ' Zongfen 'int(255)DEFAULT NULL, Deletetimedatetime,   PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=GBK Creating triggersCREATE TriggerDeletestudent beforeDelete  onStudent forEach ROWbegin     Insert  intostudentbackup (id,name,zongfen,deletetime)Values(Old.id,old.name,old.zongfen,now ());End;

Explain:
Create trigger deletestudent refers to the creation of a trigger named Deletestudent, which is used before because it is backed up before execution.

Delete on student refers to the trigger behavior when student executes the delete behavior.
The Chinese meaning of each row is in each row and is used here to indicate that when each row is affected, the trigger will be started.

Begin,end is the trigger start and end tag,

The statement between Begin and end is the behavior performed after the trigger is mobilized, and here is a new field deletetime, which is used to record the deletion time, and the method of tuning the time in MySQL I will explain in detail in other blogs.

Execute DELETE statement

Delete from student where id=3;

After executing this delete, we will find that a message from the student table is deleted, and the deleted information is inserted in the backup table.

Delete information about two tables before and after a piece of information

Before deleting

After deletion

2. Case two, use triggers for data synchronization.

Scenario: There are four messages in the student table, four of the same information in the Student 1 table, and when a message is inserted in the student table, the Student 1 table will produce the same new information (i.e. Student table 1 and Student table information are the same). The trigger behavior occurs when an insert is executed.

Student Table 1
CREATE TABLE ' student1 ' (  ' id ' int (one) not null,  ' name ' varchar (255) Default NULL,  ' zongfen ' int (255) Default NULL,  PRIMARY KEY (' id ')) engine=myisam DEFAULT CHARSET=GBK

Create a Trigger

CREATE Trigger Insert  on Student  for Each ROW begin     Insert  into student1 (Id,name,zongfen) Values (New.id,new.name,new.zongfen); End;

Execute INSERT statement

Insert  into Values (5,'e',195);

Insert before and after two table contents:

Before inserting

After inserting

　　

3. Case three: Use triggers for data backup and synchronization.

Change the information of the student table, which is more complicated than case one and case two. Because when the student table information is updated, the information is backed up before the change, and the changed information is updated to the synchronization table after the change. Words don't say much directly on the code.

Back up your data before you update
CREATEtriggerupdate on student for each ROWbegin      Insert into studentbackup (id,name,zongfen,deletetime)        values  (Old.id,old.name,old.zongfen,now ()); End after the update, synchronize the data to the synchronization table, note: We do not consider the change in the update ID this behavior,

because the ID doesn't change,  so here where id = old.id; or where id = new.id; all right,

 create  trigger  Updatestudent1 after update  on   student  for   each ROW  begin  update  student1 set  name =  New.name,zongfen =   New.zongfen  where  ID =   Old.id;  end ; 
 
 Execute UPDATE statement  

UPDATE Set = ' Z ' =  - where = 4;

Before and after data update:

Before updating

After the update

: 4. Case FOUR: Import the students ' information with the highest scores in the student's table into the Maxgrade. Words don't say much, directly on the code.

begin     DELETE  fromMaxgrade; Select Max(Zongfen) into @maxzongfen  fromstudent; SelectId into @idmax  fromStudentwhereZongfen=@maxzongfen; SelectName into @namemax  fromStudentwhereZongfen=@maxzongfen; Insert  intoMaxgrade (Id,name,zongfen) value (@idmax,@namemax,@maxzongfen);End;InsertStudentValues(4,'D',177);

Before

After

Features of the trigger:

1. The triggering behavior can only be performed before or after (Before,after).

2. The name of the trigger can be repeated (the repetition here is that two different tables can have the same trigger name, but the names of the different triggers in each table must be different, but the readability of the program should be different from the naming of each trigger in the entire database. This naming repetition seems to me to be the disadvantage of MySQL).

3. Triggers can produce additions and deletions and change the three behaviors.

4. The trigger cannot be overwritten or modified, you need to delete it before changing the trigger, and then create a new.

Delete statement drop TRIGGER trigger name;

More than one SQL statement can be inserted between 5.begin and end.

6. A stored procedure cannot be called within a trigger.

7. You can build up to 6 triggers per table, respectively, before and after adding, deleting, and adding behaviors.

8. The view does not support triggers.

Note: I have a MySQL version of 5.5.40. (the query statement is: Select version ();)

A brief talk on Starling MySQL Trigger