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. Trigger syntax code is as follows: create TRIGGER trigger_name trigger_time trigger_event on Tbl_name for Each ROW trigger_stmt 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 INSERT, LOAD The 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. In this way, you can use the same statement that is allowed in the stored subroutine 1, the user table of users copy code code is as follows: create table ' user ' ( ' id ' int (one) not NULL auto_ Increment COMMENT ' user ID ', ' name ' varchar (a) 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, ' 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 ' N 'ULL default ' COMMENT ' comment content ', primary key (' c_id ') ) engine=myisam default Charset=utf8; & Nbsp;insert into ' comment ' (' c_id ', ' u_id ', ' name ', ' content ') values (1, 1, ' Zhang Ying ', ' Trigger test '), (2, 1, ' Zhang Ying ', ' solve field redundancy Yu '), (3, 2, ' tank ', ' make the Code simpler '); here has a redundant field name, we can use federated search to find the name in the user table in the read comment, why should there be redundant fields, because the simple SQL statement execution is more efficient, But the more redundant fields, the better, the more redundant fields, the more 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 the PHP code to update, when the user is deleted, the comment table, The data about the user will be deleted 3, update the name trigger copy code as follows: delimiter | | //mysql The default end sign is a semicolon, and when you write a trigger or stored procedure with a semicolon appear, it aborts to execute drop trigger if exists updatename| | //Delete trigger with the same name, create trigger updatename after update on user for each row //build Trigger, &NBSP The begin //old,new is the record line representing the current operation, which you use as the name of the table, or if new.name!=old.name then //When the user name changes in the table, execute Update comment set comment.name=new.name where comment.u_id=old.id; end if; end| | delimiter; 4, trigger Delete comment Data copy code code is as follows: &NBSp;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; There is a bit frustrating, is to write a good trigger code, you have to delete the reconstruction, depressed, to a point is phpmyadmin, some can create a trigger, some can not, and some could create, but created to see. In the study.  5, test if the trigger is available a, test the Updata trigger copy code as follows: Update user set Name= ' Eagle ' WHERE id = 1; update after going to comment table inside to see, Inside the Name field there is no change in the section B, the test delete trigger copy code is as follows: Delete from user where id = 1; update after the comment table inside to see, inside the Name field inside the paragraph has not changed four , the advantage of the trigger 1, the trigger's "automatic" for the programmer, 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 have to write code to update the other tables in the redundant fields, I give an example, just a table, If it is a few tables have redundant fields, your code is not to write a lot of it, it seems not very uncomfortable. 2, trigger data integrity trigger has rollback, for example, I found that I like to lift, that is, you want to update five tables of data, does 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. show Triggersdrop TRIGGER updatename_copy
MySQL database triggers
