Quick understanding of the application of triggers in MySQL database _ MySQL-mysql tutorial

Quick understanding of the application of triggers in MySQL database tips: when a new integer is added to the database domain, the code segment that automatically updates the total number of running tasks is a trigger. The SQL command block that automatically records changes to a special database table is also a trigger instance.

Trigger concept: "program code automatically executed in the database to respond to certain events in a special table ." (Wikipedia) is a piece of code that is automatically activated when a special database event, such as INSERT or DELETE occurs. Triggers can be easily used for logging, automatic "stacked" changes to a single table to other linked tables, or automatic updates to table relationships. When a new integer is added to the database domain, the code segment that automatically updates the total number of operations is a trigger. The SQL command block that automatically records changes to a special database table is also a trigger instance.

The trigger is a new feature of MySQL 5.x. with the emergence of a new version of the 5.x code tree, this feature is gradually improved. In this article, I will briefly introduce how to define and use a trigger, view the trigger status, and delete the trigger after use. I will also show you an application instance of a trigger in the real world and verify its changes to database records.

Example

A simple (although artificial) instance is used to demonstrate that it is the best way to understand the MySQL trigger application. First, we create two single-domain tables. The name list is in one table (table name: data), and the number of characters inserted in the other table (table name: chars ). I want to define a trigger in the data table. each time a new name is inserted into the trigger, the total number of running tasks in the chars table is automatically updated based on the number of characters in The New inserted records.

Reference content is as follows:
Mysql> create table data (name VARCHAR (255 ));
Query OK, 0 rows affected (0.09 sec)

Mysql> create table chars (count INT (10 ));

Query OK, 0 rows affected (0.07 sec)

Mysql> insert into chars (count) VALUES (0 );

Query OK, 1 row affected (0.00 sec)

Mysql> create trigger t1 AFTER INSERT ON

Data for each row update chars SET count
= Count + CHAR_LENGTH (NEW. name );

Query OK, 0 rows affected (0.01 sec)

The key to understanding the above code is the create trigger command, which is used to define a new TRIGGER. This command creates a new trigger, assuming the name is t1. every time a new record is inserted into the data table, t1 is activated.

There are two important clauses in this trigger:

The after insert clause indicates that the trigger is activated AFTER the new record is inserted into the data table.

The UPDATE chars SET count = count + CHAR_LENGTH (NEW. name) clause indicates the SQL command executed after the trigger is activated. In this example, this command updates the chars. count column with the number of characters in the newly inserted data. name field. This information can be obtained through the built-in MySQL function CHAR_LENGTH.

The NEW keyword placed before the source table domain name is also worth noting. This keyword indicates that the trigger should consider the new value of the domain (that is, the value just inserted into the domain ). MySQL also supports the corresponding OLD prefix, which can be used to indicate the previous values of the domain.

You can call the show trigger command to check whether the TRIGGER is activated.

Reference content is as follows:
Mysql> SHOW TRIGGERSG
* *************************** 1. row ******************

? Trigger: t1

? Event: INSERT

? Table: data

Statement: UPDATE chars SET count = count + CHAR_LENGTH (NEW. name)

Timing: AFTER

? Created: NULL

Ql_mode:

1 row in set (0.01 sec)

Activate the trigger and start testing it. Try to insert several records in the data table:

Reference content is as follows:
Mysql> insert into data (name) VALUES ('Sue '), ('Jane ');
Query OK, 2 rows affected (0.00 sec)

Records: 2? Duplicates: 0? Warnings: 0

Then check the chars table to see if the trigger has completed the task:

Reference content is as follows:
Mysql> SELECT * FROM chars;
+ ------- +

| Count |

+ ------- +

| 7 |

+ ------- +

1 row in set (0.00 sec)

As you can see, the INSERT command in the data table activates the trigger, which calculates the number of characters inserted records and stores the results in the chars table. If you add another record to the data table, the chars. count value will also increase accordingly.

After the TRIGGER is applied, you can use the drop trigger command to easily delete it.

Reference content is as follows:
Mysql> drop trigger t1;
Query OK, 0 rows affected (0.00 sec)

Note: Ideally, you also need an inverted trigger. each time a record is deleted from the source table, it is subtracted from the total number of characters. This is easy to do. you can use it as an exercise. Tip: using the before delete on clause is one of the methods.

Now, I want to create an audit record to track changes to this table. This record will reflect every change in the table and show the user who made the change and the time of the change. I need to create a new table to store this information (table name: audit), as shown below.

Reference content is as follows:
Mysql> create table audit (id INT (7 ),
Balance FLOAT, user VARCHAR (50)
Not null, time timestamp not null );

Query OK, 0 rows affected (0.09 sec)

Next, I will define a trigger in the accounts table.

Reference content is as follows:

Mysql> create trigger t1 after updateon accounts

For each row insert into audit (id, balance, user, time)

VALUES (OLD. id, NEW. balance, CURRENT_USER (), NOW ());

Query OK, 0 rows affected (0.04 sec)

Note: the accounts table is updated every time. the trigger inserts the corresponding record id, New Balance, current time, and name of the user logging on to the audit table.