Summary and use of mysql triggers (triggers)

I. What triggers

1. Personal Understanding
A trigger, literally, is a trigger. For example, if it is dark, you turn on the light and you see something. You put a gun on it and lit it up. It will blow up later.
2. official definition
A trigger is a special stored procedure. Its execution is not called by a program or started manually, but triggered by an event. For example, when a table is operated (insert, delete, update) will activate it for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary.
A good feature of a trigger is that the trigger can disable or roll back changes that violate the integrity of the reference, thus canceling the data modification.
What do you mean? For example, we have all played streetscape games. If you have ever reached the next level, you have to start from the first level. The trigger root is similar.
The official explanation is as follows:
The trigger program is considered as a part of a single transaction. Therefore, the original trigger program can restore the transaction. If serious errors (such as user disconnection) are detected during the transaction ), the entire transaction is automatically restored.
The role is obvious. It can ensure the integrity of the data. The following example shows the benefits of this feature and the complexity of coding.

Ii. Trigger syntax

Copy codeThe Code is as follows:
Create trigger trigger_name trigger_time trigger_event
ON tbl_name for each row trigger_stmt
A trigger is a table-related named database object. This object is activated when a specific event occurs on the table.
The trigger program is related to the table named tbl_name. Tbl_name must reference a permanent table. You cannot associate the trigger program with the TEMPORARY table or view.
Trigger_time is the time when the program is triggered. It can be BEFORE or AFTER to indicate that the trigger program is triggered BEFORE or AFTER its statement is activated.
Trigger_event indicates the type of statements used to activate the trigger program. Trigger_event can be one of the following values:
· INSERT: the trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.
· UPDATE: the trigger program is activated when a row is changed, for example, through the UPDATE statement.
· DELETE: the trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.
Note that trigger_event is not very similar to the SQL statement used to activate the trigger program in the form of table operations, which is very important. For example, the BEFORE trigger program for INSERT can be activated by both the INSERT statement and the load data statement.
One of the examples that may cause confusion is insert .. on duplicate update... syntax: The before insert trigger will be activated for each row, followed by the after insert trigger program, or the before update and after update trigger programs, depending on whether there is a duplicate key on the row.
For a given table with the same triggering program action time and event, there cannot be two triggering programs. For example, a table cannot have two before update triggers. However, there can be one before update trigger program, one before insert trigger program, one before update trigger program, and one after update trigger program.
Trigger_stmt is the statement executed when the trigger program is activated. If you want to execute multiple statements, you can use the in... END compound statement structure. In this way, the same statements allowed in the stored subroutine can be used.

3. Create a producer

1. user table
Copy codeThe Code is as follows:
Create table 'user '(
'Id' int (11) not null auto_increment COMMENT 'user id ',
'Name' varchar (50) 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 codeThe Code is as follows:
Create table 'comment '(
'C _ id' int (11) not null auto_increment COMMENT 'comment id ',
'U _ id' int (11) not null comment 'user id ',
'Name' varchar (50) not null default ''' COMMENT 'user name ',
'Content' varchar (1000) not null default ''comment' COMMENT content ',
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, 'login', 'solving field redundancy '),
(3, 2, 'tank', 'simpler code ');

Here, there is a redundant field name. We can use joint searches to find the name in the user table when reading comments. Why should we have redundant fields? Because simple SQL statements are more efficient to execute, but the more redundant fields, the better. The more redundant fields, the more database burden.
What I want to do is: When I update the name of the user table and the trigger updates the comment table at the same time, do not write php code to update it. When the user is deleted, the comment table, data about this user will be deleted

3. Update the name trigger

Copy codeThe Code is as follows:
Delimiter | // by default, the end symbol of mysql is a semicolon. When you write a trigger or stored procedure, a semicolon appears, and the execution is aborted.
Drop trigger if exists updatename | // delete a trigger with the same name,
Create trigger updatename after update on user for each row // create a trigger,
Begin
// Old, new indicates the record row of the current operation. You can regard it as the table name or row;
If new. name! = Old. name then // when the user name in the table changes, run
Update comment set comment. name = new. name where comment. u_id = old. id;
End if;
End |
Delimiter;

4. Trigger to delete comment data

Copy codeThe Code is 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;

One thing depressing is that the code written for the trigger cannot be modified. You need to delete and recreate the code. It is depressing. Another point is phpmyadmin. Some can create and some cannot, some can be created, but cannot be seen when they are created. I am studying it.

5. test whether the trigger is available

A. Test the updata trigger.
Copy codeThe Code is as follows: update user set name = 'cangying 'where id = 1;
After the update, go to the comment table and check whether the segments in the name field have changed.

B. Test the delete trigger.
Copy codeThe Code is as follows: delete from user where id = 1;
After the update, go to the comment table and check whether the segments in the name field have changed.

Iv. Advantages of triggers

1. Trigger's "automatic"
For programmers, the trigger is invisible, but he does. If no trigger is needed, when you update the name field of the user table, you need to write code to update redundant fields in other tables. For example, if there are redundant fields in a table, do you need to write a lot of code, does it look uncomfortable.
2. Data Integrity of the trigger
The trigger has rollback. For example, I found that I like to update the data of five tables, but no two tables are updated, the other three tables are not updated.
However, if you use php code to write data, this may happen. For example, if you update the data of two tables, the database crashes. You are depressed, some are updated, and some are not updated. In this way, the page is displayed as inconsistent, and the bug changes.