MYSQL Trigger)

I. What 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 you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be

I. What 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 you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be

I. What triggers

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

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.

Trigger program and name itTbl_name.Tbl_nameA permanent table must be referenced. You cannot associate the trigger program with the TEMPORARY table or view.

Trigger_timeIs 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_eventSpecifies the type of statements used to activate the trigger program.Trigger_eventIt 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.

Please note that,Trigger_eventIt 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 into... 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_stmtIs the statement executed when the program is triggered. If you want to execute multiple statements, you can use BEGIN... 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

1. Create table 'user '(
2. 'Id' int (11) not null auto_increment COMMENT 'user id ',
3. 'Name' varchar (50) not null default ''comment' name ',
4. 'Sex' int (1) not null default '0' COMMENT '0 is male, 1 is female ',
5. Primary key ('id ')
6. ) ENGINE = MyISAM default charset = utf8;
8. Insert into 'user' ('id', 'name', 'sex') VALUES
9. (1, 'extract the stars from the start', 0 ),
10. (2, 'tank', 0 );

Id	Name	Sex
1	Extract stars from the sky	0
2	Tank	0

2. comment table comment

1. Create table 'comment '(
2. 'C _ id' int (11) not null auto_increment COMMENT 'comment id ',
3. 'U _ id' int (11) not null comment 'user id ',
4. 'Name' varchar (50) not null default ''' COMMENT 'user name ',
5. 'Content' varchar (1000) not null default ''comment' COMMENT content ',
6. Primary key ('C _ id ')
7. ) ENGINE = MyISAM default charset = utf8;
9. Insert into 'comment' ('C _ id', 'U _ id', 'name', 'content') VALUES
10. (1, 1, 'extract the stars from the start', 'trigger test '),
11. (2, 1, 'extract the stars from the start', 'resolve field redundancy '),
12. (3, 2, 'tank', 'simpler code ');

C_id	U_id	Name	Content
1	1	Extract stars from the sky	Trigger Test
2	1	Extract stars from the sky	Solve 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

1. 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.
2. Drop trigger if exists updatename | // delete a trigger with the same name,
3. Create trigger updatename after update on user for each row // create a trigger,
4. Begin
5. // Old, new indicates the record row of the current operation. You can regard it as the table name or row;
6. If new. name! = Old. name then // when the user name in the table changes, run
7. Update comment set comment. name = new. name where comment. u_id = old. id;
8. End if;
9. End |

Delimiter;

3. Trigger to delete comment data

1. Delimiter |
2. Drop trigger if exists deletecomment |
3. Create trigger deletecomment before delete on user for each row
4. Begin
5. Delete from comment where comment. u_id = old. id;
6. End |
7. 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.

4. test whether the trigger is available.

A. Test the updata trigger.

Update user set name = 'e '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.

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. "automatic" of the trigger"

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.