In-depth research on the trigger application in MySQL database

In SQL, a noun trigger is the 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 used to easily record logs and send a single table to another linked table.

In SQL, a noun trigger is the 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 used to easily record logs and send a single table to another linked table.

In SQL, a noun trigger refers to the 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.

A simple instance

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. (See list)

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 ONdata FOR EACH ROW UPDATE chars SET count = count + CHAR_LENGTH(NEW.name);Query OK, 0 rows affected (0.01 sec)

List

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, as shown in list B.

mysql> SHOW TRIGGERSG*************************** 1. row ******************?Trigger: t1?Event: INSERT?Table: dataStatement: UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)Timing: AFTER?Created: NULLql_mode:1 row in set (0.01 sec)

List B

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

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:

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.

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. (List C)

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)

List C

Next, I will define a trigger in the accounts table. (List D)

mysql> CREATE TRIGGER t1 AFTER UPDATEON accountsFOR 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)

List D

If you have reached this step, it is easy to understand. Each time an UPDATE is performed in the accounts table, the trigger INSERT corresponds to the record id, new balance, current time, and the name of the user logging on to the audit table.