One, what triggers
1, personal understanding
Triggers, which are literally understood, a hair-trigger device, a trigger (haha, personal understanding), for example, like when it's dark, you turn on the light, you see something. You put the firecracker on, lit it up and blew it up in a minute.
2, official definition
A trigger (trigger) is a special stored procedure that is not executed by a program or by hand, but by events, such as when an action is performed on a table (Insert,delete, update). Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in dba_triggers, user_triggers data dictionaries.
A very good feature of triggers is that triggers can suppress or rollback changes that violate referential integrity, eliminating attempted data modifications.
What do you mean, for example, the arcade game we all played it, break through a pass, the next pass, there is a clearance did not go through the first pass. The trigger root is similar.
The official explanation is as follows
Triggers are considered to be part of a single transaction, so the transaction can be restored by the original trigger, and the entire transaction is automatically restored if a serious error is detected during the transaction (such as a user interruption line).
His role is obvious, you can take care of the integrity of the data, there is an example below to illustrate his benefits, and if the writing code is not so complex
Second, trigger syntax
Copy Code code as follows:
CREATE TRIGGER trigger_name trigger_time trigger_event
On tbl_name for each ROW trigger_stmt
A trigger is a named database object that is related to a table and is activated when a specific event occurs on the table.
The trigger is associated with a table named Tbl_name. Tbl_name must refer to a permanent table. You cannot associate a trigger with an temporary table or view.
Trigger_time is the action time that triggers the 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 a trigger when inserting a new row into a table, for example, through INSERT, LOAD data, and replace statements.
· Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.
· Delete: Activates a trigger when a row is deleted from a table, for example, through the Delete and replace statements.
Note that it is important that the trigger_event is not very similar to the SQL statement that activates the triggering program as a table operation. For example, the before trigger for inserts can be activated not only by an INSERT statement, but also by the load data statement.
One of the examples that can cause confusion is insert into. On DUPLICATE UPDATE ... Syntax: Before insert triggers are activated for each row, followed by an insert trigger, or before update and after update triggers, depending on whether there are duplicate keys on the row.
There can be no 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 a before insert trigger, or 1 before update triggers and an after UPDATE trigger.
TRIGGER_STMT is the statement that executes when the trigger is activated. If you are going to execute multiple statements, you can use the BEGIN ... End Compound statement structure. In this way, you can use the same statements that are allowed in the storage subroutine
Third, create the hair-picker
1, Users table user
Copy Code code as follows:
CREATE TABLE ' user ' (
' id ' int (one) not NULL auto_increment COMMENT ' user ID ',
' Name ' varchar ' not NULL default ' COMMENT ' name ',
' Sex ' int (1) Not NULL default ' 0 ' COMMENT ' 0 is male, 1 is female ',
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8;
INSERT into ' user ' (' id ', ' name ', ' sex ') VALUES
(1, ' Zhang Ying ', 0),
(2, ' tank ', 0);
2, Comment table comment
Copy Code code as follows:
CREATE TABLE ' comment ' (
' c_id ' int (one) not NULL auto_increment COMMENT ' Comment ID ',
' u_id ' int (one) not NULL COMMENT ' user ID ',
' Name ' varchar ' not NULL default ' COMMENT ' User name ',
' Content ' varchar (1000) not NULL default ' COMMENT ' comments ',
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 ', ' resolve field redundancy '),
(3, 2, ' tank ', ' make Code simpler ');
Here's a redundant field name, we can read comments in the user table to find the name, why there are redundant fields, because simple SQL statement execution more efficient, but not redundant fields as much as possible, redundant fields, the same will increase the burden of the database.
What I'm going to do is, when I update the name of the user table, the trigger updates the comment table, not the PHP code to update, and when the user is deleted, the data about the user is deleted in the comment table.
3, update the name trigger
Copy Code code as follows:
Delimiter | | MySQL default end symbol is a semicolon, when you write a trigger or stored procedure with a semicolon appears, will be aborted to execute
Drop trigger if exists updatename| | Deletes a trigger with the same name.
Create trigger UpdateName After update on the user for each row//set up a trigger,
Begin
Old,new is the record line that represents the current operation, and you can take it as a table name.
If New.name!=old.name then//when the user name in the table changes, execute
Update comment set comment.name=new.name where comment.u_id=old.id;
End If;
end| |
delimiter;
4, Trigger deletes comment data
Copy Code code as follows:
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;
A bit very depressing, is to write a good trigger code, can not modify, you want to delete the reconstruction, depressed, there is a point is phpmyadmin, some can create triggers, some cannot, some can create, but created to see. In the study.
5, test whether the trigger is available
A, test updata triggers
Copy Code code as follows:
Update user set Name= ' eagle ' where id = 1;
After the update to the comment table inside to see if the Name field inside the paragraph has not changed
b, test the Delete trigger
Copy Code code as follows:
Delete from user where id = 1;
After the update to the comment table inside to see if the Name field inside the paragraph has not changed
Four, the advantages of the trigger
1, the "automatic" of the trigger
Triggers are invisible to programmers, but he did do things, if you do not use the trigger, you update the user table name field, you have to write code to update the other table redundant fields, I give an example, just a table, if a few tables have redundant fields, Does your code have a lot to write about and it doesn't seem to be very unpleasant.
2, data integrity of triggers
The trigger has rollback, for example, I find that I like to raise the child, that is, you want to update five tables of data, will not appear updated 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 two of the table of data, this time, the database hung up. You are depressed, some updated, some did not update. This page does not display the same, there are bugs.
